Say, we have table_1 like - unit | req_type A | 1 A | 2 B | 2 B | 3 D | 2 E | 2 E | 4 We have to write a SQL such that, we can select Unit with Req_Type=2 Also, that Unit should not have any other Req_Type in list. Ex - 1,3 Solution - We can add another column such that - case when req_type in (1,3) then 1 else 0 end col1 unit | req_type | col1 A | 1 | 1 A | 2 | 0 B | 2 | 0 B | 3 | 1 D | 2 | 0 E | 2 | 0 E | 4 | 0 Then we can do a group by and take count(col1) unit | c A | 1 B | 1 D | 0 E | 0 Then we can select all rows which have count = 0 Compete SQL - select unit from (select unit, sum(col1) c from (select unit, req_type, case when req_type in (1,3) then 1 else 0 end col1 from table_1) group by unit) where c=0
