title: MySQL left join with max id
body: <p>I Have a table called tbl_messages the containes messages. Each message has a value of 0 under MessageReply if it's the first message and a message id if it is a reply to another message.</p>
  
  <p>I need to get for each root message it's data + the date of it's latest response.</p>
  
  <p>I'm running the following SQL procedure:</p>
  
  <pre><code>SELECT 
      m1.*, m2.MessageID, m2.MessageDate 
  FROM 
      tbl_messages as m1 LEFT JOIN tbl_messages as m2 
  ON 
      m1.MessageID = m2.MessageReply 
  WHERE 
      m1.MessageReply = '0' AND 
      (m1.MessageUser = '1' OR m1.MessageBusiness = '1') AND 
      m2.MessageID = (SELECT MAX(MessageID) FROM tbl_messages WHERE MessageReply = m1.MessageID) 
  ORDER BY 
      m2.MessageID desc, m1.MessageID desc
  </code></pre>
  
  <p>My problem is the because of the last WHERE statement it doesn't return values from the left table that doesn't have values on the right table.</p>
  
  <p>How can I solve it?</p>