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

Question

I have 3 tables as shown below. I want to get a list of member names, ids, together with the latest activity time for each of them, given a particular member name search term.

This 'latest activity time' is the latest time from comparing both the game_record and the eating_record tables.

The desired output is also 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 is 'john':

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

What I have tried so far:

I can get the first 2 columns by the following query:

select name, 
       m_id 
from 
       member_info 
where 
       name like "john%";

And I can get the latest_time for a given member by the following query, but I'm not sure how to merge this second query with the first one to get my desired output.

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

SQL Fiddle for this is available at: http://sqlfiddle.com/#!2/b70d0/3

P.S. The tables game_record and eating_record have other columns that are particular to games/food that are not included here, since I wanted to simplify and isolate the part where I needed help.

Thank you! :)

It would be easier for us to help you if you set up a [SQL Fiddle](http://sqlfiddle.com/) — John Conde, May 02 '13 at 18:40

Taryn · Accepted Answer · 2013-05-02T19:11:03.860

You could use a UNION ALL query to get the eating_record and game_record times in the same column, then apply an aggregate function to get the max time:

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

See SQL Fiddle with Demo.

This could also be written with aggregates in the subquery:

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

See SQL Fiddle with Demo

score 0 · Answer 2 · answered May 02 '13 at 19:00

My solution is to aggregate each of the tables (games and eating) by member id to get the latest time. Then, join these together and use the greatest() function to get the latest time:

select mi.*, greatest(gr.maxtime, er.maxtime) as latest_time
from member_info mi left outer join
     (select m_id, MAX(time_of_activity) as maxtime
      from game_record gr
      group by m_id
     ) gr
     on gr.m_id = mi.m_id left outer join
     (select m_id, MAX(time_of_activity) as maxtime
      from eating_record er
      group by m_id
     ) er
     on er.m_id = mi.m_id

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

2 Answers2