MySQL: get all users who haven't done any training in 2 years -

i need users haven't carried out training in last 2 years.

we run 3 courses year: basic 1 in january, basic 2 in february , basic 3 in march.

not needs training each year must have completed course in last 2 years in order keep registration. @ present through each user record , highlight user if none of training courses occurred in last 2 years but, number of users increases, need check users @ once i've been looking @ queries might provide data , display date of last training , name of course if haven't completed training in last 2 years.

so, instance, given 2 tables here:

users_temp: +----+-------+ | id | name  | +----+-------+ | 1  | david | | 2  | john  | | 3  | barry | | 4  | mary  | +----+-------+  courses_temp: +---------+------------+---------+ | user_id | date       | name    | +---------+------------+---------+ | 1       | 2015-01-01 | basic 1 | | 1       | 2015-02-02 | basic 2 | | 1       | 2015-03-03 | basic 3 | | 2       | 2015-01-01 | basic 1 | | 2       | 2014-02-02 | basic 2 | | 2       | 2014-03-03 | basic 3 | | 3       | 2012-01-01 | basic 1 | | 3       | 2012-02-02 | basic 2 | | 3       | 2013-03-03 | basic 3 | | 4       | 2013-01-01 | basic 1 | | 4       | 2012-02-02 | basic 2 | | 4       | 2012-03-03 | basic 3 | +---------+------------+---------+ 

i can manually check , tell last training david took in march year, john last took training in january year, barry took training in march 2013 , mary took training in january 2013 need produce table this:

+---------+-------+---------------+-------------+ | user_id | name  | last training | last course | +---------+-------+---------------+-------------+ | 3       | barry | 2013-03-03    | basic 3     | | 4       | mary  | 2013-01-01    | basic 1     | +---------+-------+---------------+-------------+ 

my first query went this:

select      user_id,      max(date)      courses_temp group      user_id having     max(date) < date_sub(now(),interval 2 year) 

to these results:

+---------+------------+ | user_id | max(date)  | +---------+------------+ | 3       | 2013-03-03 | | 4       | 2013-01-01 | +---------+------------+ 

to name of user added:

select      user_id,      max(date),     users_temp.name      courses_temp join     users_temp on     courses_temp.user_id = users_temp.id group      user_id having     max(date) < date_sub(now(),interval 2 year) 

to these results:

+---------+------------+-------+ | user_id | max(date)  | name  | +---------+------------+-------+ | 3       | 2013-03-03 | barry | | 4       | 2013-01-01 | mary  | +---------+------------+-------+ 

to try , name of last course used this

select      user_id,      max(date),     users_temp.name,     courses_temp.name      courses_temp join     users_temp on     courses_temp.user_id = users_temp.id group      user_id having     max(date) < date_sub(now(),interval 2 year) 

to these results:

+---------+------------+-------+---------+ | user_id | max(date)  | name  | name    | +---------+------------+-------+---------+ | 3       | 2013-03-03 | barry | basic 1 | | 4       | 2013-01-01 | mary  | basic 1 | +---------+------------+-------+---------+ 

but that's wrong, should be:

+---------+------------+-------+---------+ | user_id | max(date)  | name  | name    | +---------+------------+-------+---------+ | 3       | 2013-03-03 | barry | basic 1 | | 4       | 2013-01-01 | mary  | basic 3 | +---------+------------+-------+---------+ 

how correct course name (basic 3) come through please?

it looks you're on complicating things. left join takes care of finding recent training attended - if there no row in courses_temp c2 same id, , more recent date corresponding row in courses_temp c1, null values allow identify recent row per user. after that, it's gravy.

select u.*, c1.name, c1.date   users_temp u     inner join courses_temp c1       on u.id = c1.user_id     left join courses_temp c2       on u.id = c2.user_id , c1.date < c2.date   c2.date null , c1.date < now() - interval 2 year; 

your last attempt great example of mysqls helpful group by processing being bit of bugbear. can't rely on pick correct value field not present in group by constraint, or not used in aggregate formula. see 12.16.3 mysql handling of group by more details

mysql extends use of group select list can refer nonaggregated columns not named in group clause [ ... ] can use feature better performance avoiding unnecessary column sorting , grouping. however, useful when values in each nonaggregated column not named in group same each group. server free choose value each group, unless same, values chosen indeterminate.