Selecting rows from a table with specific values per id

I have the below table

Table 1

Id   WFID  data1   data2
1    12    'd'     'e'
1    13    '3'     '4f'
1    15    'e'     'dd'
2    12    'f'     'ee'
3    17    'd'     'f'
2    17    'd'     'f'
4    12    'd'     'f'
5    20    'd'     'f'

From this table I just want to select the rows which has 12 and 17 only exclusively. Like from the table I just want to retrieve the distinct id's 2,3 and 4. 1 is excluded because it has 12 but also has 13 and 15. 5 is excluded because it has 20.

• 2 in included because it has just 12 and 17.
• 3 is included because it has just 17
• 4 is included because it has just 12

If you just want the list of distinct ids that satisfy the conditions, you can use aggregation and filter with a having clause:

select id
from mytable
group by id
having max(case when wfid not in (12, 17) then 1 else 0 end) = 0

This filters out groups that have any wfid other than 12 or 17.

If you want the entire corresponding rows, then window functions are more appropriate:

select
from (
    select t.*,
        max(case when wfid not in (12, 17) then 1 else 0 end) over(partition by id) flag
    from mytable t
) t
where flag = 0