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

help

hi

i have table with folowing columns (subid,itemid)

i want to write select stmt to get subid where itemid =all group of value('1','2')

like this

Select subid from subscriptionItem where itemId in all('1','2')

but this stmt not work

help me

Somthing like this ?

Select SubID From SubscriptionItem Where ItemID in ('1','2')|||

Hi,

If you mean you want those SubID that have both ItemID 1 and 2, then the above will not work.

what you should is..

Select SubID From subscriptionItem Where ItemID in (1,2) group by SubID Having count(0) =2

Assuming that the combination SubID and ItemID is primary (unique).

If they are not unique, which I doubt, then try:

Select SubID FROM (Select distinct SubID, ItemID from SubscriptionItem where ItemID in (1,2)) si Group by SubID having count(0) = 2

Hope this helps