title: Left join to get most recent entries
body: <p>I have the tables <code>users</code> and <code>statuses</code> . I want to select all the users, plus any statuses they might have, but only the most recent status from each user.</p>
  
  <p>Here is the code that doesn't work:</p>
  
  <pre><code>SELECT users.id, alias, gender, login, logout, users.create_date, statustext as statustxt, TIMESTAMPDIFF(YEAR,birthdate,CURDATE()) AS age
                  FROM users
                  LEFT JOIN statuses s ON users.id = s.user_id
                  WHERE s.ID = (                              
                          SELECT MAX(s2.ID)                    
                          FROM statuses s2
                          WHERE s2.user_id = s.user_id         
                          )
  </code></pre>
  
  <p>This gets the users with the most recent statuses, but not the users from the users table as well. Maybe it can be fixed by some small adjustment?</p>
  
  <p>I got the sub query by searching, but I don't understand how that code works. It seems to compare two versions of the same table (For example: <code>WHERE s2.user_id = s.user_id</code> ) . Where can I read about this sort of technique? 
  Is a sub query required in this case by the way?</p>
  
  <p>If you can find a solution would be great, and some basic explanation of how it works would highly appreciated.</p>
  
  <p>----------EDIT---------------</p>
  
  <p>I took one of the responses (by maresa) and combined with the sub query of my initial code , and this works(!) It has 3 selects and looks a bit over complicated maybe?:</p>
  
  <pre><code> SELECT users.id, alias, gender, login, logout, users.create_date, statustext as statustxt, TIMESTAMPDIFF(YEAR,birthdate,CURDATE()) AS age
  FROM users
      LEFT JOIN (
          SELECT user_id, statustext FROM statuses s
           WHERE s.ID = (                             
             SELECT MAX(s2.ID)                
             FROM statuses s2
             WHERE s2.user_id = s.user_id
       )
    ) as s ON users.id = s.user_id
  </code></pre>