Error in subquery in sqlserver 2008 [Resolved]

Posted by Klbaiju under Sql Server on 7/29/2016 | Points: 10 | Views : 155 | Status : [Member] | Replies : 1
Hi

this is a working query

select conductor_id,conductor_id2 from routeBusdetails where uname='prathap' and tour_date='2016-08-01' and conductor_id is not null




my requirement is to write above query in sub query like following

select conductor_id,cname from routeconductormaster where uname='prathap' and conductor_id not in(select conductor_id,conductor_id2 from routeBusdetails where uname='prathap' and tour_date='2016-08-01' and conductor_id is not null )


following error is showing
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.


how to solve this

Regards

Baiju




Responses

Posted by: Rajnilari2015 on: 7/29/2016 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Klbaiju Sir,
Change your query to

select conductor_id,cname 

from routeconductormaster
where uname='prathap'
and conductor_id not in(select conductor_id from routeBusdetails
where uname='prathap'
and tour_date='2016-08-01'
and conductor_id is not null )


The reason for which your query didnot worked is because of the fact that the subquery was returning multiple columns e.g. conductor_id,conductor_id2 which you was trying to grab in a single column conductor_id and that is not possible.

Hope that helps

--
Thanks & Regards,
RNA Team

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response