It should be a simple query

3 Mar 2006

Get a list of users and the most recent training course they have attended. This should be a very simple query with a sub-select, but I’m using MySQL 3.23 and sub-selects are not available, so queries have to be rewritten using joins. (Sub-selects are available from MySQL 3.24 onwards). Took me a while:

select
   t1.*
from
   usertraining as t1
   left join usertraining as t2
   on t1.user_id = t2.user_id and t1.training_date<t2.training_date
where
   t2.training_date is null

This matches up the training courses. When the date is at its maximum (ie. the most recent) there are no matching dates in the joined table which are greater than that – so the t2 entries for that row are Null – and we use the where clause to pick out exactly those rows.
Another way to do it:

select
   t1.*
from
   usertraining as t1
   left join usertraining as t2
   on t1.user_id = t2.user_id
group by
   t1.training_date having t1.training_date = MAX(t2.training_date)
where
   t2.training_date is null

And of course, to get the user details is then very simple:

select
    users.*, usertraining.*
from
   users left join
   usertraining as t1 ON users.user_id = t1.user_id
   left join usertraining as t2
   on t1.user_id = t2.user_id and t1.training_date
where
   t2.training_date is null

Recent Posts