Error with using a sub-query

Question

I need an output that will display the TripName, [Type], FirstName, LastName and [NumberOfGuides] from this query, but I get an error stating:

"Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,

, >= or when the subquery is used as an expression."

select TripName, [Type], FirstName, LastName, (select count(*)
from Guide g, TripGuide tr, Trip t 
where g.GuideNum = tr.GuideNum and t.TripID = tr.TripID
group by TripName, Type 
having count(*) > 1 ) as [NumberOfGuides]
from Guide, Trip
order by NumberOfGuides desc

What is the goal of the query? What do you want to see? And where do each of your columns come from? We can't tell — kjmerf, Mar 02 '17 at 16:45
Possible duplicate of [SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=](http://stackoverflow.com/questions/2653188/sql-server-subquery-returned-more-than-1-value-this-is-not-permitted-when-the-s) — Ricardo C, Mar 02 '17 at 16:46
The problem is the `GROUP BY` in your subquery. That means for each combination of TripName and Type, there will be one value returned. — DVT, Mar 02 '17 at 17:45
Please see the following link to help you write joins better: https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — KindaTechy, Mar 02 '17 at 17:52

rfb · Answer 1 · 2017-03-02T16:59:31.967

0

Even if I can't decipher exactly your query, move out of the count from the subquery:

select TripName, [Type], FirstName, LastName, count(
    select *
    from Guide g, TripGuide tr, Trip t
    where g.GuideNum = tr.GuideNum and t.TripID = tr.TripID
    group by TripName, Type
    having count(*) > 1 ) as [NumberOfGuides]
from Guide, Trip
order by NumberOfGuides desc

Apart from that it seems there is a logic error in the subquery because there's no link with the top query.

So i guess the column NumberOfGuides always reports the same value.

edited Mar 02 '17 at 16:59

answered Mar 02 '17 at 16:50

rfb

1,107
1
7
14

Thanks for the response but It's still not working. When I run the subquery alone it executes and displays the correct output. But if I try to join with the TripName, [Type], FirstName, LastName it gives an error – Mar 02 '17 at 17:07
Sorry but your comment sounds like a no-brainer. It's clear that your query suffers from many shortcomings and my answer highlighted macroscopics. I suggest you to begin an in depth study of mechanism you're trying to implement. For example, just look around to related posts: i'm just seeing [Join vs. sub-query](http://stackoverflow.com/questions/2577174/join-vs-sub-query). Or look at the link suggested by @RicardoC (whose flag could close definetely the matter). – rfb Mar 03 '17 at 08:43

score 0 · Answer 2 · answered Mar 02 '17 at 18:36

I do not understand the requirement of showing the number of guides, where you already showing the available guide names. Normally for this kind of resulting columns, the number of guides will be 1, as for that trip there will be only 1 guide with that first and last name available. If you can provide us the goal of requirement and a sample required outcome you are expecting, would be useful help you.

Error with using a sub-query

2 Answers2