Returning a List of Customers and Average Rental Amounts (Sakila)

Question

So I need a query done, but I am getting this error. I have not worked with MySQL for that long, so I'm sure it is something to do with me not understanding how MySQL works.

Database: http://dev.mysql.com/doc/sakila/en/
Diagram: https://dev.mysql.com/doc/workbench/en/images/wb-sakila-eer.png

I'm mostly working with customers, rental, payment.

I need a list of customers, with their average rental amount, and their most recent rental/return dates.

SELECT 
       DISTINCT customer.customer_id, customer.last_name, customer.first_name,
       customer.email, AVG(payment.amount) 'Average Rental', 
       max(rental.rental_date) 'Most Recent Rental Date',
       max(rental.return_date) 'Most Recent Return Date'
FROM   customer
 INNER JOIN rental 
    ON rental.rental_id = payment.rental_id 
   AND customer.customer_id = rental.customer_id
 INNER JOIN payment 
    ON payment.rental_id = rental.rental_id 
   AND payment.customer_id = customer.customer_id
GROUP BY customer_id
ORDER BY customer.last_name;

Error:

Error Code: 1054. Unknown column 'payment.rental_id' in 'on clause'

Can someone explain why it is giving this error, even though the column exists?
Also, how I can go about resolving this situation.

Answer 1

Your join conditions are in the wrong order. You cannot reference a table in an on clause until you have included it in the from. In fact, you have repeated the same condition twice. Try this:

SELECT customer.customer_id, customer.last_name, customer.first_name,
       customer.email, AVG(payment.amount) "Average Rental",
       max(rental.rental_date) as "Most Recent Rental Date", 
       max(rental.return_date) as "Most Recent Return Date"
FROM customer INNER JOIN
     rental
     ON customer.customer_id = rental.customer_id INNER JOIN
     payment
     ON payment.rental_id = rental.rental_id AND
        payment.customer_id = customer.customer_id
GROUP BY customer.customer_id
ORDER BY customer.last_name;

Notice that I also changed the single quotes to double quotes for the column identifiers. Don't use single quotes for identifiers; this generally leads to confusion. Use double quotes or backticks.

And, I removed the distinct keyword. It is generally unnecessary when using group by.

And I added the table name to the group by. Otherwise, you'll get an ambiguous column error.