Monday, March 12, 2012

help ! using multiple fields with IN condition !

Hi All,

I am trying to do something like this

Select * from Table 1 where (field1, field2, field3) IN (select field1, field2, field3 from table2 where fieldx = 'rt')

This can be done in oracle. Is there any equivalent in SQL Server ?

Can anybody please help !

Thanks.

PN

You can use a correlated sub-query


Select *
From Table1 t1
Where Exists(
Select *
From Table2 t2
Where t2.fieldx = 'rt'
And t1.field1 = t2.field1
And t1.field2 = t2.field2
And t1.field3 = t2.field3
)

or a derived table and a join.

Select *
From Table1 t1
Join (
Select *
From Table2
Where t2.fieldx = 'rt'
) as t2
On t1.field1 = t2.field1
And t1.field2 = t2.field2
And t1.field3 = t2.field3
)

|||

Thanks ! the first query works nicely !

PN

No comments:

Post a Comment