0

I have a table with these two columns

+------+------+
|id    |type  |
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|2     |B     |
+------+------+
|3     |A     |
+------+------+
|3     |B     |
+------+------+
|3     |C     |
+------+------+
|4     |A     |
+------+------+
|4     |A     |
+------+------+

and I want to get rows only with duplicate id that has value A and B for Type column

It should look something like this:

+------+------+
|id    |type  |
+------+------+
|1     |A     |
+------+------+
|1     |B     |
+------+------+
|3     |A     |
+------+------+
|3     |B     |
+------+------+

I tried the query below which does print out rows with only the duplicate ids but wasn't able to get rows with value A and B only

select id, type from table s1
where (select count(id) from table s2 where s2.id = s1.id and type in ('A', 'B')) > 1 
group by id, type
order by id
tiredqa_18
  • 162
  • 2
  • 9
  • @a_horse_with_no_name wasn't sure why this was marked as duplicate. the similar question doesn't really solve this as i only want rows with duplicate ids. the other solution will get the rows with value a and b but it will get for the non duplicate id as well. i cant seem to combine the query above (to get rows with duplicate id) and the solution that is marked similar which is why this question was posted. – tiredqa_18 Jan 12 '21 at 07:00
  • @horse_with_no_name thanks for re-opening. appreciate it – tiredqa_18 Jan 12 '21 at 08:14

1 Answers1

1

You can use the count analytical function as follows:

select id, type from
(select id, type, count(distinct type) over (partition by id) as cnt
  from t where type in ('A','B')) t
where cnt = 2

You can also use EXISTS as follows:

select id, type from your_table  t 
 where type in ('A','B')
   and exists (select 1 from your_table tt
                where t.id = tt.id 
                  and tt.type in ('A','B')
                  and tt.type <> t.type)
Popeye
  • 35,427
  • 4
  • 10
  • 31