richersoon richersoon - 2 months ago 6
MySQL Question

MySQL - Return the last record on the second table then return all in the first table

I have two tables customers and orders, below is the structure.

Table - contacts

id


Table - orders

id
contact_id


How can I select all from contacts table but only select the latest record from the orders table?

SELECT contacts.*,
Max(orders.id)
FROM contacts
LEFT JOIN orders
ON contacts.id = orders.contact_id
GROUP BY contacts.id;


But I always gets
NULL
if I use
LEFT JOIN
, it only have value if I use
INNER JOIN
.

Answer

select the latest record in orders and group it first

select contacts.*, orders.id
from contacts
left join (select max(id) as id, contact_id
           from orders
           group by contact_id) orders
     on contacts.id = orders.contact_id
Comments