I'm trying to create a SQL query for an inventory report. I have thus far created a SQL query that successfully brings back unique part numbers along with their description and Quantity on Hand. I would like to add the customer name to my report but since we sell some parts to multiple customers, adding this causes duplicates in part numbers and Quantities on hand, despite the DISTINCT statement. Is there a way to limit results to only one customer listed per part? I am new to SQL so I imagine I am missing something obvious. Here is my query:
select distinct [Part].[PartNum] as [Part_PartNum]
, [Part].[PartDescription] as [Part_PartDescription]
, [PartBin].[OnhandQty] as [PartBin_OnhandQty]
from Erp.Part as Part
inner join Erp.ShipDtl as ShipDtl on Part.Company = ShipDtl.Company
And Part.PartNum = ShipDtl.PartNum
inner join Erp.PartBin as PartBin on Part.Company = PartBin.Company
And Part.PartNum = PartBin.PartNum
order by Part.PartNum
Using Epicor, I see...
Is there a reason you're joining the ShipDtl table, since your select statement is only pulling back information from the Part and PartBin tables? Try the following:
SELECT p.PartNum, PartDescription, OnHandQty
FROM erp.Part AS Part INNER JOIN
erp.PartBin AS PartBin ON Part.Company = PartBin.Company and Part.PartNum = PartBin.PartNum
Please note also that if your company uses multiple bins, you will have to do a summation on the OnHandQty field in order to get the total for that company, which will also require a group by statement.