Select only rows that don't have duplicates in certain fields

[aleceiffel]

I need to create a select statement that pulls 10 fields from a table but only when fields 1 and 2 are unique. Ex table with 4 fields:

company1 555-555-1234 Jenn Smith
company1 555-555-5555 Steve Smith
company2 555-555-7777 Jenn Long
Company3 555-555-1234 Shaun Morre
Company4 555-555-4444 Fred Smith

This would need to return only lines 3 and 4 because these are the only 2 that have a unique company name AND phone number.

I've tried using "select company, count(company)" then "group by company" type statements but this only works for the company column and the rest of the fields are not there.

Does anyone have any suggestions on how to tackle this?

 

[PHV]

SELECT A.*
FROM yourTable A
INNER JOIN (SELECT company FROM yourTable GROUP BY company HAVING COUNT(*)=1) B ON A.company=B.company
INNER JOIN (SELECT phone FROM yourTable GROUP BY phone HAVING COUNT(*)=1) C ON A.phone=C.phone

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

aleceiffel, any follow up ?