mysql - SQL - Joining on subquery condition -

in database have 2 tables:

action

+--------------+--------------+------+-----+---------+----------------+ | field        | type         | null | key | default |          | +--------------+--------------+------+-----+---------+----------------+ | id           | int(11)      | no   | pri | null    | auto_increment | | lead_id      | int(11)      | yes  | uni | null    |                | | type         | varchar(255) | no   |     | null    |                | +--------------+--------------+------+-----+---------+----------------+ 

lead

+---------+--------------+------+-----+---------+----------------+ | field   | type         | null | key | default |          | +---------+--------------+------+-----+---------+----------------+ | id      | int(11)      | no   | pri | null    | auto_increment | | status  | varchar(255) | yes  |     | null    |                | | created | datetime     | no   |     | null    |                | | lead_id | int(11)      | no   | mul | null    |                | +---------+--------------+------+-----+---------+----------------+ 

lead table unusual because leads non-unique , identified lead_id, there can several leads same lead_id.

what i'd retrieve actions , join them respective leads on condition they're recent (max(created)?).

this answer partially explains how deal leads table, can't find out how make join on subquery right way.

there multiple ways this. following puts condition in on clause:

select a.* lead l join      action      on l.lead_id = a.lead_id ,         l.created = (select max(created) leads l2 l2.lead_id = l.lead_id) 

you can explicit aggregation subquery:

select a.* lead l join      action      on l.lead_id = a.lead_id  join      (select lead_id, max(created) maxcreated       lead l       group lead_id     ) lmax     on lmax.lead_id = l.lead_id , lmax.maxcreated = l.created;