subquery inside case? - 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

.NET Inside SQL Server (2005)

subquery inside case?

Author

Topic

asksql
Starting Member

11 Posts

Posted - 2008-06-08 : 22:52:25

Hello,

I am trying to do something like this:

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA

But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.

How do I do this?

vaseem
Starting Member

1 Post

Posted - 2008-07-09 : 10:52:59

Hi,

there are cases where subquery returns more than one row in this case always use top 1 statement like below

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select top 1 table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA

Vaseem

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-09 : 12:48:55

I think you need MAX ....and Group by.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources