Eliminate Records Without Combination

Question

I have the following data in TEMP_DATA table

From these records, I would like to get only those records which are having CAT_NO 36 and 39 combination of records, ideally my expected results are as follows

How can I do this?

Answer 1

I don't have an Oracle8 instance to test but this should work:

select * from temp_data where prod_no in ( 
    select prod_no from temp_data where cat_no = 39 and prod_no in (
       select prod_no from temp_data where cat_no = 36 ) ) ;