CODEWITHSUNDEEP
sql-servercountdistinct
Deb
Deb

Reputation: 753

Count query help needed with unique records

I have this following data-structure and I am trying to find unique ID where value Alphabets value Equals to A and B (i.e. select ID = 1, but not ID = 3 or 5). and also find unique ID where value Alphabets values contains Both A and B (i.e. select ID = 1, 3 and 5). 

╔═══╦════════════╦═════════════╗
║   ║ ID         ║ Alphabets   ║
╠═══╬════════════╬═════════════╣
║ 1 ║ 1          ║ A           ║
║ 1 ║ 1          ║ B           ║
║ 4 ║ 2          ║ B           ║
║ 5 ║ 2          ║ D           ║
║ 6 ║ 3          ║ A           ║
║ 7 ║ 3          ║ B           ║
║ 8 ║ 3          ║ D           ║
║ 9 ║ 4          ║ A           ║
║ 10║ 4          ║ C           ║
║ 11║ 5          ║ A           ║
║ 12║ 5          ║ B           ║
║ 13║ 5          ║ C           ║
║ 14║ 5          ║ D           ║
╚═══╩════════════╩═════════════╝

CREATE TABLE dbo.Alphabets(
ID int NOT NULL
,Alphabets char(1) NOT NULL
,CONSTRAINT PK_Alphabets PRIMARY KEY (ID, Alphabets)
);


INSERT INTO dbo.Alphabets VALUES
 (1, 'A')
,(1, 'B')
,(2, 'B')
,(2, 'D')
,(3, 'A')
,(3, 'B')
,(3, 'D')
,(4, 'A')
,(4, 'C')
,(5, 'A')
,(5, 'B')
,(5, 'C')
,(5, 'D');

Upvotes: 1

Views: 25

Answers (1)

spaghettidba
spaghettidba

Reputation: 529

This should do:

SELECT ID
FROM Alphabets
WHERE Alphabets IN ('A','B')

EXCEPT

SELECT ID
FROM Alphabets
WHERE Alphabets NOT IN ('A','B')

There are many other ways to accomplish the task, but this is one of the simplest.

A more obscure (but more efficient) solution in a single pass uses aggregation and a CASE expression:

SELECT ID
FROM Alphabets
GROUP BY ID
HAVING MAX(CASE WHEN Alphabets IN ('A','B') THEN 0 ELSE 1 END) = 0

Upvotes: 2

Related Questions