I have three tables in MySQL:
customer (id, name)
status (id, name)
customer_status (id, idCustomer, idStatus, date)
Table customer_status may contain several records appearing in the customer table and status table.
I want to get the list of customers and their actual status (one record per customer with last update date).
I tried this:
select customer.id, customer.name, status.name, customer_status.date
from customer
inner join customer_status on customer_status.idCustomer = customer.Id
inner join status on status.id = customer_status.idStatus
order by customer_status.date desc
group by customer.id
But the results are not fine. How would I do that?