CODEWITHSUNDEEP
sqlms-access
toolshed
toolshed

Reputation: 2019

How do I return IDs for which only a single record is returned in SQL?

I have currently trimmed a section of my data set, but I need to filter it further.

My goal is to isolate only those IDs for which a single row is returned.

For example, with a data set as follows:

ID          Client Name                  Client Manager
1           XYZ Corp.                    Ralph
2           Bob's Marketplace            Marianne
2           Bob's Marketplace            David
3           Davis Auto Repair            Ralph
3           Davis Auto Repair            Marianne
4           Everything Inc.              David
5           Pet World                    Marianne

I need to return:

ID          Client Name                  Client Manager
1           XYZ Corp.                    Ralph
4           Everything Inc.              David
5           Pet World                    Marianne

Upvotes: 0

Views: 103

Answers (4)

SQLMason
SQLMason

Reputation: 3275

SELECT ID, 
       Client_Name, 
       Client_Manager
FROM table_name
WHERE ID IN 
(
    SELECT ID 
    FROM table_name
    GROUP BY ID
    HAVING COUNT(*) = 1
)

Upvotes: 1

chetan
chetan

Reputation: 2896

SELECT *
FROM your_table a, (SELECT ID FROM your_table
                    GROUP BY ID
                    HAVING COUNT(*) = 1) b
WHERE a.ID = b.ID;

see SQLFiddle DEMO

Upvotes: 0

Luis LL
Luis LL

Reputation: 2993

SELECT ID, [Client Name], min([Client Manager]) as [Client Manager]
FROM dbo.Table1
GROUP BY ID, [Client Name]
HAVING COUNT(*) = 1

Upvotes: 0

bvr
bvr

Reputation: 4826

Try this

SELECT ID, [Client Name], [Client Manager] FROM Table1
WHERE ID IN 
(
  SELECT ID FROM Table1
  GROUP BY ID
  HAVING COUNT(ID) = 1
)

Upvotes: 0

Related Questions