CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Micheal Pike
Micheal Pike

Reputation: 11

SQL Server selecting with self join

I have a table that has records like this:

FieldId  collationid  Type    Message 
---------------------------------------------
1         1234         WC     hello  
2         1234         WR     next message
3         1234         WZ     again  
4         1234         WX     another message 
5         ab12         WC     this message 
6         ab12         WR     again  
7         ab12         WZ     misc message 
8         5678         WC     hello  
9         5678         WR     next message  
10        5678         WZ     again  
11        5678         WX     another message

A recordset is complete when it has all four records, a WC, WR, WZ and WX. I need a sql that shows me when a record is missing. In the previous table example, the SQL would produce ab12 because it only has WC, WR and WZ records.

Appreciate any help you can give me..

Upvotes: 1

Views: 33

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Use COUNT() and HAVING :

SELECT collationid
FROM tbl
WHERE Type IN('WC', 'WR', 'WZ', 'WX')
GROUP BY collationid
HAVING COUNT(DISTINCT Type) < 4

Upvotes: 3

Related Questions