Select only rows that don't have duplicates in certain fields
Select only rows that don't have duplicates in certain fields
(OP)
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?
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?
Talk To Other Members
RE: Select only rows that don't have duplicates in certain fields
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Select only rows that don't have duplicates in certain fields