subquery for IN clause - SQL Server Forums

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)

subquery for IN clause

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2008-05-13 : 10:43:27

Hi,

Being ignorant I just tried to write a query with an IN clause that looked like this:


and cv.criteriaId in (select valueId from #locations)

SQL Server accepted this as valid and ran the query, returning no results.

However if I rewrite the query manually with the thirteen or so numbers that that query produces, like this:


and cv.valueID in (1001, 7600, 7601, 7602, 7603, 7604, ..etc..)

It runs fine and produces what I need.

How can I write my subquery so that the whole query produces the value set I'm expecting?

Cheers,
Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 10:45:18

The reason for this quite clear.
The first query above is different from the second one.

First query:
and cv.criteriaId in (select valueId from #locations)

Second query:
and cv.valueID in (1001, 7600, 7601, 7602, 7603, 7604, ..etc..)

You are not even matching same column.

E 12°55'05.25"
N 56°04'39.16"