retrieve rows of 1st column based on common data on 2nd column

Question

Say I have input like this:

1st column ---------2nd column
23 ---------------------- 0080
23 ---------------------- 0010
23 ---------------------- 0080
25 ---------------------- 0010
25 ---------------------- 0010
34 ---------------------- 0080
27 ---------------------- 0010
27 ---------------------- 0080

I want to retrieve all the rows of 1st column which has both 0080 and 0010 data in 2nd column. result will be like this:

1st column--------- 2nd column
23 ---------------------- 0080
23 ---------------------- 0010
23 ---------------------- 0080
27 ---------------------- 0010
27 ---------------------- 0080

From result we can see that 1st column doesn't include 25 as 25 has only 0010 in 2nd column, and same for 34 which has only 0080 in 2nd column.

I tried using nested query, but it is becoming very slow as my table is very large (contains around 30,000+ rows). I am looking for smart technique which is faster for large data table.

Answer 1

select * from your_table
where col1 in 
(
   select col1
   from your_table
   where col2 in ('0080', '0010')
   group by col1
   having count(distinct col2) = 2
)