Error in subquery in sqlserver 2008 [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	@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

@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

Latest Posts