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

aleceiffel · Nov 26, 2010

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 · Nov 27, 2010

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 · Dec 8, 2010

aleceiffel, any follow up ?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

aleceiffel

IS-IT--Management

PHV

MIS

PHV

MIS

Similar threads

Part and Inventory Search

Sponsor