mysql - getting latest timestamp from 2 joined tables for each row output in select statement -

i have 3 tables shown below. want list of member names, ids, latest activity time each of them, given particular member name search term.

this 'latest activity time' latest time comparing both game_record , eating_record tables.

the desired output given below.

member_info table:

name     m_id ----     ----  john      1 doe       2 johnson   3 

game_record:

time_of_activity      game_id  m_id ----------------      -------  ----- 2013-01-20 23:01:00     1         3 2013-01-20 23:01:07     4         1 2013-01-20 23:01:06     1         2 2013-01-20 23:01:05     3         1 

eating_record:

time_of_activity      food_id  m_id ----------------      -------  ----- 2013-01-20 23:01:04     1         1 2013-01-20 23:01:03     4         1 2013-01-20 23:01:02     1         2 2013-01-20 23:01:01     3         3 

desired output when search term 'john':

name     m_id     latest_time ----     ----     ----------- john      1       2013-01-20 23:01:07 johnson   3       2013-01-20 23:01:01 

---

what have tried far:

i can first 2 columns following query:

select name,         m_id          member_info          name "john%"; 

and can latest_time given member following query, i'm not sure how merge second query first 1 desired output.

select greatest ((select                          max(time_of_activity)                                          game_record                        join                            member_info on game_record.m_id = member_info.m_id                   member_info.name = "john"),                  (select                          max(time_of_activity)                                          eating_record                        join                            member_info on eating_record.m_id = member_info.m_id                   member_info.name = "john")); 

sql fiddle available at: http://sqlfiddle.com/#!2/b70d0/3

p.s. tables game_record , eating_record have other columns particular games/food not included here, since wanted simplify , isolate part needed help.

thank you! :)

you use union all query eating_record , game_record times in same column, apply aggregate function max time:

select m.name,   m.m_id,   max(time_of_activity) latest_time member_info m left join (   select time_of_activity, m_id   game_record   union   select time_of_activity, m_id   eating_record ) g   on m.m_id = g.m_id m.name 'john%' group m.name, m.m_id 

see sql fiddle demo.

this written aggregates in subquery:

select m.name,   m.m_id,   max(time_of_activity) latest_time member_info m left join (   select max(time_of_activity) time_of_activity, m_id   game_record   group m_id   union   select max(time_of_activity) time_of_activity, m_id   eating_record   group m_id ) g   on m.m_id = g.m_id m.name 'john%' group m.name, m.m_id; 

see sql fiddle demo