Subquery returns multiple rows in HAVING clause

Question

I want to get the customer who mostly borrowed films of category 3 in 2016, July

SELECT c_firstName, c_lastName, rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID HAVING rental.c_ID=MAX((SELECT COUNT(rental.c_ID) 
FROM customer, copies, rentalprocess, rental, film
WHERE customer.c_ID=rental.c_ID AND rentalprocess.r_ID=rental.r_ID AND
      rentalprocess.s_ID=copies.s_ID AND film.f_ID=copies.f_ID AND
      f_category=3 AND r_date LIKE "2016-07%" GROUP BY rental.c_ID))

But ir doesn't work because it said that the subquery returns more than one row

What can I do?

Answer 1

Max() is an aggregate function that needs to be in a select statement

SELECT 
  c_firstName
  , c_lastName
  , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
  (
  select 
    MAX(i.iID) 
  from 
    (
    SELECT 
      COUNT(rental.c_ID) iID
    FROM customer, copies, rentalprocess, rental, film
    WHERE 
      customer.c_ID=rental.c_ID AND 
      rentalprocess.r_ID=rental.r_ID AND
      rentalprocess.s_ID=copies.s_ID AND 
      film.f_ID=copies.f_ID AND
      f_category=3 
      AND r_date LIKE "2016-07%" 
    GROUP BY rental.c_ID
    ) i
  )

In this case the sub-select returns multiple rows but then you take the max value of that query

Comment from Mr Linoff is correct, you should use explicity joins:

SELECT 
  c_firstName
  , c_lastName
  , rental.c_ID
FROM customer, rental
GROUP BY rental.c_ID 
HAVING rental.c_ID=
  (
  select 
    MAX(i.iID) 
  from 
    (
    SELECT 
      COUNT(rental.c_ID) iID
    FROM 
      customer 
      inner join rental 
        on customer.c_ID=rental.c_ID
      inner join rentalprocess 
        on rentalprocess.r_ID=rental.r_ID
      inner join copies 
        on rentalprocess.s_ID=copies.s_ID
      inner join film on film.f_ID=copies.f_ID
    WHERE 
      f_category=3 
      AND r_date LIKE "2016-07%" 
    GROUP BY rental.c_ID
    ) i
  )