Find Duplicate 2

[markypoos]

I have a table where i am trying to stop duplicate entries of a product. Table example:
UniqueID MainID ProductID
====== ====== =========
001 AA ABC123
002 AA ABC456
003 AA ABD789
004 BB ABC123
005 BB ABC456

SO, if MAINID = 'AA' AND (there is more than one instance of product 'ABC123') then i would like it to return the offending ProductID/s that have more than one entry for MAINID 'AA'.
Hope this makes sense
ps. I cannot do this using indexes - must be queried.

 

[Glasgow]

I think something like:

SELECT MainId FROM YourTable M1 WHERE M1.MainId IN (Select MainId FROM YourTable WHERE MainId=M1.MainId AND UniqueId <> M1.UniqueId)

might do the trick

 

[Glasgow]

I must learn to read posts properly!

SELECT MainId,ProductId FROM YourTable M1 WHERE M1.ProductId IN (Select ProductId FROM YourTable WHERE MainId=M1.MainId AND ProductId=M1.ProductId AND UniqueId <> M1.UniqueId)

might be closer. But I have not tested it.

 

[sneki]

select mainid, productid
from table
group by mainid, productid
having count(*) > 1

 

[markypoos]

Thanks Glasgow / sneki - perfect