asked on

SQL Join Query - returning duplicates - need distinct values only

Here is my query

SELECT DISTINCT c.PartNumber, SP.SerialNumber, SP.MAC
FROM Order C
INNER Join SO_MAC SP
ON C.SO = SP.SO
Where c.SO='847403'

The problem is there are only 30 SerialNumber & MAC addresses and it's returning 60 for this current query.

The SO (Sales Order) has 2 line items - so I think it has something to do with that....if I change it to this (LI = LineItem)...Then it returns all 30 unique SerialNumber & MAC Addresses for the product.

SELECT DISTINCT c.PartNumber, SP.SerialNumber, SP.MAC
FROM Order C
INNER Join SO_MAC SP
ON C.SO = SP.SO
Where c.SO='847403' AND c.LI='1'

That's great that the above works and functions for what I need - but why if there are more than 1 "LineItems" does the JOIN create the duplicate results - and how should I be doing this query the right way so it doesn't change the results based on the number of "LineItems" on an "Order".

virtuadept

Yes, if SO is not unique then the join will have duplicate rows.

If SO + LI = unique and are in both tables you could do this:

SELECT DISTINCT c.PartNumber, SP.SerialNumber, SP.MAC
FROM Order C
INNER Join SO_MAC SP
ON C.SO = SP.SO
AND C.LI = SP.LI
Where c.SO='847403'

ProdigyOne2k

ASKER

The only thing "SP" and "C" tables have in common is the "SO"

virtuadept

The DISTINCT should be making the rows unique. Can you post some example output?

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ProdigyOne2k

ASKER

This is the problem - I have normalized tables mixed with unnormalized tables causing the duplicates - no way to fix this the "right way" without doing some restructuring of the data