CODEWITHSUNDEEP
sqlsql-serverepicorerp
Alice Elizabeth
Alice Elizabeth

Reputation: 3

duplicates in SQL

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:

SQL 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

Upvotes: 0

Views: 161

Answers (2)

Steve Lovell
Steve Lovell

Reputation: 2574

Something like the following might work. It uses a subquery to identify a relevant single company for the part in question.

I've assumed there is also another table "Company" where you might want to pull some company details from rather than simply the "Company" field on which you are joining. You'll see that I've just taken the "minimum" Company to be the one to return. There are other ways to do the same thing. You could instead take the minimum (or maximum or somethings else) company from the "Part" table instead. Let me know how you get on.

select
    [Part].[PartNum] as [Part_PartNum]
    , [Part].[PartDescription] as [Part_PartDescription]
    , [Company].[CompanyDesc] as [CompanyDesc]
    , [PartBin].[OnhandQty] as [PartBin_OnhandQty] 
from
    Erp.Part as Part 
    inner join
    (
     select
        ShipDtl.PartNum,
        min(ShipDtl.Company) Company
     from
        Erp.ShipDtl
     group by
        ShipDtl.PartNum
    ) as Part1Company ON
        Part.Company = Part1Company.Company AND
        Part.PartNum = Part1Company.PartNum
    inner join Erp.PartBin as PartBin on
        Part.Company = PartBin.Company And
        Part.PartNum = PartBin.PartNum 
    inner join Erp.Company as Company on
        Part.Company = Company.Company
order by
    Part.PartNum

Upvotes: 2

user2572833
user2572833

Reputation: 146

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.

Upvotes: 1

Related Questions