Unknown column in 'having clause'

Question

I need to find in sakila database the longest rental period of a movie. I have tried this:

  SELECT DISTINCT
      customer.first_name
    FROM
      rental,
      customer
    WHERE
      rental.customer_id = customer.customer_id
    GROUP BY
      rental.rental_id
    HAVING
      (
        rental.return_date - rental.rental_date
      ) =(
      SELECT
        MAX(countRental)
      FROM
        (
        SELECT
          (
            rental.return_date - rental.rental_date
          ) AS countRental
        FROM
          rental,
          customer
        GROUP BY
          rental.rental_id
      ) AS t1
    )

but I am getting the error:

# 1054 - Unknown column 'rental.return_date' in 'having clause'

Does anybody know why? I have used a column that's supposed to be the aggregated data. What am i missing?

Schmo

Answer 1

As written in the documentation

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

You have to specify return_date and rental_date in the select clause.

There are two options:

SELECT DISTINCT
  customer.first_name,
  rental.return_date,
  rental.rental_date
FROM
  rental,
  customer
WHERE
  rental.customer_id = customer.customer_id
GROUP BY
  rental.rental_id
HAVING
  (
    rental.return_date - rental.rental_date
  ) =(
  ...

or

SELECT DISTINCT
  customer.first_name,
  (rental.return_date - rental.rental_date) as rental_duration
FROM
  rental,
  customer
WHERE
  rental.customer_id = customer.customer_id
GROUP BY
  rental.rental_id
HAVING
  rental_duration =(
  ...

Both should work just fine.

Tricolor

thank you for answering. in which select clause ? i got 3. and i only want their minus operator result – Schmo

The topmost SELECT. I have updated the answer with two possibilities. – Tricolor

only the first option worked, but i actually need the second one, because i need to show the duration. the second option gives the same error – Schmo

This second one should work also... Can you try again? (I have fixed one error just after the first edit, maybe you used query before corrections). Can you say what version of MySQL are you using? – Tricolor

The mentioned MySQL extension is ONLY_FULL_GROUP_BY sql_mode. More info see dev.mysql.com/doc/refman/5.6/en/… – Pitchford