mysql - One to Many Join with Aggregate Function (Max) -

i have 2 tables.

table 1:

+---------+---------+ | lead_id | deal_id | +---------+---------+ |    2323 | null    | |    2324 | 1199    | |    2325 | null    | |    2326 | null    | |    2327 | 1080    | +---------+---------+ 

table 2:

+---------+-------------+-------------+------------+ | deal_id | stage_from  |  stage_to   | changedate | +---------+-------------+-------------+------------+ |    1199 | incoming    | stage1      | 01-dec-14  | |    1199 | stage1      | incoming    | 05-dec-14  | |    1199 | incoming    | stage1      | 12-dec-14  | |    1080 | incoming    | unqualified | 06-dec-14  | |    1080 | unqualified | stage2      | 07-dec-14  | 

i add "changedate" each deal_id has record in table 2 stage changed "incoming" "stage1". in cases stage changed "incoming" "stage1" multiple times, want maximum date i.e. december 12, 2014 in example rather december 1, 2014.

resulting table should be:

+---------+---------+------------+ | lead_id | deal_id | changedate | +---------+---------+------------+ |    2323 | null    | null       | |    2324 | 1199    | 12-dec-14  | |    2325 | null    | null       | |    2326 | null    | null       | |    2327 | 1080    | null       | +---------+---------+------------+ 

i looked @ few similar questions recommend using "group_concat", can't want method.

any suggestions appreciated.

here's query:

select  t.lead_id, t.deal_id, date_format(tt.maxchangedate,'%d-%b-%y') changedate table1 t left join  (     select       deal_id,      max(changedate) maxchangedate      table2    stage_from = 'incoming'    , stage_to = 'stage1'    group deal_id ) tt on tt.deal_id = t.deal_id; 

sql fiddle demo

output:

lead_id deal_id changedate 2323    (null)  (null) 2324    1199    12-dec-14 2325    (null)  (null) 2326    (null)  (null) 2327    1080    (null) 

explanation:

let's @ following query (inside tt block in above query )

   select       deal_id,      max(changedate) maxchangedate      table2    stage_from = 'incoming'    , stage_to = 'stage1'    group deal_id; 

this query gets latest changedate each deal_id if have incoming , stage1 stage_from , stage_to respectively.

so query returns following result:

deal_id    maxchangedate 1199        2014-12-12 

now if make left join between table1 , result result each in entry table1 having corresponding maxchangedateright after lead_id , deal_id.