Category: SQL Server

29 Jul 2016 00:14

Joined:	04/08/2008
Level:	Silver
Points:	116

Points:	5

Error showing in subquery in sqlserver

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

#767384

29 Jul 2016 00:54

Joined:	03/05/2013
Level:	Gold
Points:	15822

Points:	4

Hi,

In your SubQuery you are calling 2 cloumns but you are checking condition with 1 column which is not correct while wrote in subquery you just choose which column is required in subquery and use that particular column alone


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 )

I assume that conductor_id2 is not required so that i removed that field, you shouldn't give 2 fields while using subquery in filtration time.

--------------------------------------------------------------------------------
Give respect to your work, Instead of trying to impress your boss.

N@veen
Blog : http://naveens-dotnet.blogspot.in/

#767385

29 Jul 2016 01:50

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	4

Issue is in "not in(select conductor_id,conductor_id2", you can not compare two columns together. Can you try the following query. That will be help your requirement.


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 )
  and 
 conductor_id not in
 (select conductor_id2 from routeBusdetails where uname='prathap' and tour_date='2016-08-01' and conductor_id is not null )
 )

By Nathan
Direction is important than speed

#767387

29 Jul 2016 02:33

Joined:	29/05/2009
Level:	Gold
Points:	10380

Points:	0

Hi,

We can return Sub Query for one column only. Sub Query Returns one column only so you meet this
error try to change single column only then only we canot do .

Name : Dotnet Developer-2015
Email Id : kumaraspcode2009@gmail.com

'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

Error showing in subquery in sqlserver

About dotnetspider.com

Quick Links

General

Services