CODEWITHSUNDEEP
sqlsyntax-error
Radostina Georgieva
Radostina Georgieva

Reputation: 1

Error in code. Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

I'm trying to run this query but no matter what I do I keep getting this error and I cannot work it out. Any help would be appreciated.

select
  CC.ContactID, 

from 
  Client C 
  join Contacts CC on CC.ContactID = C.ContactID 
 where 

        ClientID In (
    
                                  Select 
                                    ClientID,Sum(Total-allocated) as Bal 
                                  from 
                                    Main 
                                  Where 
                                    Total <> Allocated
                                    and NomTypeID < 10 
                                  Group By 
                                    ClientID
                                  HAVING Sum(Total-allocated) > 10
                             )

Upvotes: 0

Views: 50

Answers (2)

Chananel P
Chananel P

Reputation: 1814

I'm not sure what DB kind you are using (so I also could not test my answer).

But in general when you use IN you need the subquery to return only one column.

So the query should be (removed the Sum(Total-allocated) as Bal):

select
  CC.ContactID, 

from 
  Client C 
  join Contacts CC on CC.ContactID = C.ContactID 
 where 

        ClientID In (
    
                                  Select 
                                    ClientID 
                                  from 
                                    Main 
                                  Where 
                                    Total <> Allocated
                                    and NomTypeID < 10 
                                  Group By 
                                    ClientID
                                  HAVING Sum(Total-allocated) > 10
                             )

Upvotes: 1

Jim Macaulay
Jim Macaulay

Reputation: 5155

Please use below query,

select
  CC.ContactID from  
Client C 
join Contacts CC on CC.ContactID = C.ContactID 
join (Select ClientID,Sum(Total-allocated) as Bal from Main Where 
Total <> Allocated and NomTypeID < 10 
Group By ClientID HAVING Sum(Total-allocated) > 10)) m
on (C.ClientID = m.ClientID);

Upvotes: 0

Related Questions