title: Returning a List of Customers and Average Rental Amounts (Sakila)
body: <p>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.</p>
  
  <p><strong>Database</strong>: <a href="http://dev.mysql.com/doc/sakila/en/" rel="nofollow">http://dev.mysql.com/doc/sakila/en/</a><br>
  <strong>Diagram</strong>: <a href="https://dev.mysql.com/doc/workbench/en/images/wb-sakila-eer.png" rel="nofollow">https://dev.mysql.com/doc/workbench/en/images/wb-sakila-eer.png</a></p>
  
  <p>I'm mostly working with customers, rental, payment.</p>
  
  <p>I need a list of customers, with their average rental amount, and their most recent rental/return dates. </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>Error Code: 1054. Unknown column 'payment.rental_id' in 'on clause'
  </code></pre>
  
  <p>Can someone explain why it is giving this error, even though the column exists?<br>
  Also, how I can go about resolving this situation.</p>