Can I use (Col1, col2) in clause in SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can I use (Col1, col2) in clause in SQL?

Author

Topic

rameshusa
Starting Member

1 Post

Posted - 2008-12-31 : 04:54:46

Hi,

In oracle, I can use where (Col1, col2) in ( select field1, field2 from table.... ). It is equal to writing col1 = (select col1 from ...) and col2 = (select col2 from ... ), however not repeating the sub query twice.

I am getting Incorrect syntax near ','. Is anything I am missing here...can't I use where (Col1, Col2) IN (select field1, field2...)

Thanks for your time.

Ramesh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 04:58:21

nope in sql server you need to use

 
where Col1 in (select field1 from table.... )
and  col2 in (select  field2 from table.... )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 05:00:09

another way is to use join


select...
from table t
...
join (select field1, field2 from table....)tmp
on tmp.field1=t.col1
and tmp.field2=t.col2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-31 : 05:06:37

quote:
Originally posted by visakh16

nope in sql server you need to use

where Col1 in (select field1 from table.... )
and col2 in (select field2 from table.... )

Nope. The Col1 and Col2 values may not be paired from same record...

Use EXISTS...

SELECT t1.*
FROM Table1 AS t1
WHERE EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.col1 = t1.col1 AND t2.col2 = t1.col2)

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 05:11:22

quote:
Originally posted by Peso

quote:
Originally posted by visakh16

nope in sql server you need to use

where Col1 in (select field1 from table.... )
and col2 in (select field2 from table.... )

Nope. The Col1 and Col2 values may not be paired from same record...

Use EXISTS...

SELECT t1.*
FROM Table1 AS t1
WHERE EXISTS (SELECT * FROM Table2 AS t2 WHERE t2.col1 = t1.col1 AND t2.col2 = t1.col2)

E 12°55'05.63"
N 56°04'39.26"

ah...yes...that makes sense
thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources