Oracle SQL: How to select only ID‘s which are member in specific groups?

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.

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 groupIDs 11 and 12, and to no other group. This works because 11 and 12 are sequential numbers.

Other options: if you want ids 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 ids 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