CODEWITHSUNDEEP
sql-server
Rudrakster
Rudrakster

Reputation: 23

Unable to figure out query

I have a Table organized as follows:

ID  Group
1   I
1   O
1   M
2   I
2   N
2   O
3   M
4   I
5   O
5   M

I need to find all IDs are Group M and either group I or O. In this case group 1 and 5.

Thanks!

Upvotes: 2

Views: 35

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use grouping with conditional aggregates in HAVING clause:

SELECT Id 
FROM mytable
WHERE [Group] IN ('I', 'O', 'M') 
GROUP BY Id
HAVING COUNT(CASE WHEN [Group] = 'M' THEN 1 END) > 0 AND 
       COUNT(CASE WHEN [Group] IN ('O', 'I') THEN 1 END) > 0

Demo here

Upvotes: 3

Related Questions