title: MySQL: get all users who haven't done any training in 2 years
body: <p>I need to get all users who haven't carried out any training in the last two years. </p>
  
  <p>We run 3 courses a year: Basic 1 in January, Basic 2 in February and Basic 3 in March.</p>
  
  <p>Not everyone needs to do the training each year but everyone must have completed a course in the last 2 years in order to keep their registration. At present we look through each User record and highlight the user if none of their training courses occurred in the last 2 years but, as the number of Users increases, we need to check all Users at once so I've been looking at queries that might provide the data and display the date of their last training and the name of the course if they haven't completed any training in the last 2 years.</p>
  
  <p>So, for instance, given the two tables here:</p>
  
  <pre><code>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 |
  +---------+------------+---------+
  </code></pre>
  
  <p>I can manually check and tell that the last training which David took was in March this year, John last took training in January this year, Barry took training in March 2013 and Mary took training in January 2013 so I need to produce a table like this:</p>
  
  <pre><code>+---------+-------+---------------+-------------+
  | user_id | Name  | Last Training | Last Course |
  +---------+-------+---------------+-------------+
  | 3       | Barry | 2013-03-03    | Basic 3     |
  | 4       | Mary  | 2013-01-01    | Basic 1     |
  +---------+-------+---------------+-------------+
  </code></pre>
  
  <p>My first query went like this:</p>
  
  <pre><code>SELECT 
      user_id, 
      max(date)
  FROM 
      courses_temp
  GROUP BY 
      user_id
  HAVING
      max(date) &lt; DATE_SUB(NOW(),INTERVAL 2 YEAR)
  </code></pre>
  
  <p>To get these results:</p>
  
  <pre><code>+---------+------------+
  | user_id | max(date)  |
  +---------+------------+
  | 3       | 2013-03-03 |
  | 4       | 2013-01-01 |
  +---------+------------+
  </code></pre>
  
  <p>To get the name of the user I added:</p>
  
  <pre><code>SELECT 
      user_id, 
      max(date),
      users_temp.name
  FROM 
      courses_temp
  JOIN
      users_temp
  ON
      courses_temp.user_id = users_temp.id
  GROUP BY 
      user_id
  HAVING
      max(date) &lt; DATE_SUB(NOW(),INTERVAL 2 YEAR)
  </code></pre>
  
  <p>To get these results:</p>
  
  <pre><code>+---------+------------+-------+
  | user_id | max(date)  | name  |
  +---------+------------+-------+
  | 3       | 2013-03-03 | Barry |
  | 4       | 2013-01-01 | Mary  |
  +---------+------------+-------+
  </code></pre>
  
  <p>To try and get the name of the last course I used this</p>
  
  <pre><code>SELECT 
      user_id, 
      max(date),
      users_temp.name,
      courses_temp.name
  FROM 
      courses_temp
  JOIN
      users_temp
  ON
      courses_temp.user_id = users_temp.id
  GROUP BY 
      user_id
  HAVING
      max(date) &lt; DATE_SUB(NOW(),INTERVAL 2 YEAR)
  </code></pre>
  
  <p>To get these results:</p>
  
  <pre><code>+---------+------------+-------+---------+
  | user_id | max(date)  | name  | name    |
  +---------+------------+-------+---------+
  | 3       | 2013-03-03 | Barry | Basic 1 |
  | 4       | 2013-01-01 | Mary  | Basic 1 |
  +---------+------------+-------+---------+
  </code></pre>
  
  <p>But that's wrong, it should be:</p>
  
  <pre><code>+---------+------------+-------+---------+
  | user_id | max(date)  | name  | name    |
  +---------+------------+-------+---------+
  | 3       | 2013-03-03 | Barry | Basic 1 |
  | 4       | 2013-01-01 | Mary  | Basic 3 |
  +---------+------------+-------+---------+
  </code></pre>
  
  <p>How do I get the correct course name (<strong>Basic 3</strong>) to come through please?  </p>