Get a subset from another table

[gussifinknottle]

Hello,

I would like to retrieve all of the data from table1 wherein two of the columns match two columns in table2.

I have done a select * from table1 where col1 in (select col1 from table2) and col2 in (select col2 from table2)

I am not getting the right number though? What do I need to modify?

Thanks,
Gussi.

 

[IanWaterman]

YOu would be better joining tables

select t1.* from table1 t1, table2 t2
where
t1.col1= t2.col1

Ian

 

[gussifinknottle]

Thanks Ian.

 

[Dagon]

The only problem with the join is that table2 may not be uniquely keyed on col1 and col2, so it's possible this could increase the number of rows your query returns. If that's the case, an alternative would be to use an EXISTS:

select * from table1 
where 
exists
(select 1 
from table2 t2
where t2.col1 = t1.col1
and   t2.col2 = t1.col2)