---------------------------------------------------- -- data of the OP: select * from (values(1,'A'),(2,'A'),(3,'A'),(3,'B'),(4,'B')) as f(id, type); select * from ( select id , sum(case type when 'A' then 1 else 0 end) as a_count , sum(case type when 'B' then 1 else 0 end) as b_count from (values(1,'A'),(2,'A'),(3,'A'),(3,'B'),(4,'B')) as f1(id, type) -- your_table group by 1 ) as f2 where a_count > 0 and b_count > 0; ----------------------------------------------------