You must Sign In to post a response.
  • Category: SQL Server

    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
    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
    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
    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.


Sign In to post your comments