Help getting Subquery to work....

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)

Help getting Subquery to work....

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2010-05-05 : 10:32:07

I receive the below message when attempting to run the below query (which is actually a view). I know returning more than 1 value is not allowed. Any suggestions on how to get this to work within a view?

"Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."


select c.sym, c.company, e.first + ' ' + e.last as account_manager,       

chkho = (
select cc.company from company cc
inner join ps_bridge psbb on psbb.co_id = cc.co_id
inner join product pp on pp.pro_id = psbb.pro_id
where 
pp.pro_id = 001
and psbb.ho_co_id = cc.co_id),

chkhostate = (
select cc.state_ from company cc
inner join ps_bridge psbb on psbb.co_id = cc.co_id
inner join product pp on pp.pro_id = psbb.pro_id
where 
pp.pro_id = 001
and psbb.ho_co_id = cc.co_id)

from company c

inner join employee e on e.employee_id = c.account_manager_id
inner join ps_bridge psb on psb.co_id = c.co_id
inner join product p on p.pro_id = psb.pro_id

where 
p.pro_id = 001

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-05-05 : 10:37:09

Just this should do, no?

select c.sym
, c.company
, e.first + ' ' + e.last as account_manager       
, c.company
, c.state
from company c
inner join employee e on e.employee_id = c.account_manager_id
inner join ps_bridge psb on psb.co_id = c.co_id
inner join product p on p.pro_id = psb.pro_id
where p.pro_id = 001

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:47:53

you've an extra join with employee in main query so not sure if given suggestion is same as your original, but you can get a start from it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources