Unknown column in 'having clause'

Viewed 43924

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?

1 Answers
Related