0

I would like to know what's the best solution to prevent this error in case of greater than operator : As mentioned in this question Subquery returned more than 1 value. This is not permitted when the subquery , the solution in the case of = is to replace the equals operator to In and the problem will be fixed. but what about the case when I have > greater operator.

What can I do in this case?

What can replace the > operator to solve this issue?

select userId 
from Users,table o where
Users.isActive=1 and o.UserId = Users.UserId
group by UserId 
having o.HourStart > (select Hour 
                      from Hours h, table b where b.HoursId = Hours.HoursId
                      group by Hour)
  • 2
    Please provide sample data, desired results, and an explanation of what you want the query to do. Also learn to use proper, explicit, **standard**, readable `JOIN` syntax. – Gordon Linoff Apr 21 '21 at 12:17
  • 2
    It's as simple as making sure that `select Hour from Hours h, Users group by Hour` only returns one result. A number of ways to handle that but that's most likely not going to return the result you expect. What exactly do you want to select? Show some input and expected output. – Lieven Keersmaekers Apr 21 '21 at 12:20
  • 2
    That subquery is pretty pointless. It will *duplicate* the `Hour` value for every `User` row and then *deduplicate* it with `Group By`. It's an expensive way to execute `select distinct Hour from Hours`. Unless `Hours` contains a single value `Hour` value though, this will return multiple values. Which of them should the server compare with `HourStart` ? The fist? Last? The middle one? A random one? – Panagiotis Kanavos Apr 21 '21 at 12:22
  • What are you trying to do? What do you trying to compare? This isn't something that can be fixed by replacing one operator with another – Panagiotis Kanavos Apr 21 '21 at 12:25
  • I will modify the query –  Apr 21 '21 at 12:38
  • In this context what is `>` supposed to mean, greater than any or greater than all? – Charlieface Apr 21 '21 at 13:32
  • I mean greater than all , all rows selected –  Apr 21 '21 at 13:33
  • Try changing your subquery to select the MAX value of Hour from the joined tables. – tlemaster Apr 21 '21 at 14:08
  • but I need the condition `o.HourStart > (select Hour from Hours h,...)` , I need to use greater operator from this field to all rows –  Apr 21 '21 at 15:08
  • @abdou31 - like I said, show some inputs and expected outputs – Lieven Keersmaekers Apr 21 '21 at 17:58

1 Answers1

0

There is a set-based ALL and ANY operator, which compares a value to all of a set.

Notes:

  • Use proper join syntax, not commas, which were deprecated nearly 30 years ago
  • You must put the filter in a where not a having because it is not part of the aggregation (it's also normally more efficient this way)
  • group by is unnecessary in an exists-type subquery unless it also comes with a having inside the subquery
select u.userId 
from Users u
join table o on o.UserId = u.UserId
where u.isActive=1
  and o.HourStart > ALL (
                      select Hour 
                      from Hours h
                      join table b on b.HoursId = h.HoursId)
group by u.UserId

But most people find it highly confusing.

It is often easier to express these as an EXISTS or NOT EXISTS, this means you need to flip the > to <=:

select u.userId 
from Users u
join table o on o.UserId = u.UserId
where Users.isActive=1
  and not exists (select 1
                      from Hours h
                      join table b on b.HoursId = h.HoursId
                      where h.Hour <= o.HourStart)
group by u.UserId

See more on this by Brad Schulz

Charlieface
  • 52,284
  • 6
  • 19
  • 43