I want to select only those ID‘s which are in specific groups. For example:
ID GroupID 1 11 1 12 2 11 2 12 2 13
Here I want to select the ID’s which are in the groups 11 and 12 but in no other groups. So the result should show just the ID 1 and not 2. Can someone provide a SQL for that? I tried it with
SELECT ID FROM table WHERE GroupID = 11 AND GroupID = 12 AND GroupID != 13;
But that didn’t work.
Advertisement
Answer
You can use aggregation:
select id from mytable group by id having min(groupID) = 11 and max(groupID) = 12
This having
condition ensures that the given id belongs to groupID
s 11 and 12, and to no other group. This works because 11 and 12 are sequential numbers.
Other options: if you want id
s that belong to group 11 or 12 (not necessarily both), and to no other group, then:
having sum(case when groupId in (11, 12) then 1 end) = count(*)
If numbers are not sequential, and you want id
s in both groups (necessarily) and in no other group:
having max(case when groupID = 11 then 1 end) = 1 and max(case when groupID = 12 then 1 end) = 1 and max(case when groupID in (11, 12) then 0 else 1 end) = 0