title: JOIN only one row from second table and if no rows exist return null
body: <p>In this query I need to show all records from the left table and only the records from the right table where the result is the highest date.</p>
  
  <p>Current query:</p>
  
  <pre><code>SELECT  a.*, c.*
  FROM users a 
  INNER JOIN payments c
      ON a.id = c.user_ID
  INNER JOIN
  (
      SELECT user_ID, MAX(date) maxDate
      FROM payments
      GROUP BY user_ID
  ) b ON c.user_ID = b.user_ID AND
          c.date = b.maxDate
  WHERE a.package = 1
  </code></pre>
  
  <p>This returns all records where the join is valid, but I need to show all users and if they didn't make a payment yet the fields from the payments table should be null.</p>
  
  <p>I could use a union to show the other rows:</p>
  
  <pre><code>SELECT  a.*, c.*
  FROM users a 
  INNER JOIN payments c
      ON a.id = c.user_ID
  INNER JOIN
  (
      SELECT user_ID, MAX(date) maxDate
      FROM payments
      GROUP BY user_ID
  ) b ON c.user_ID = b.user_ID AND
          c.date = b.maxDate
  WHERE a.package = 1
  union
  SELECT  a.*, c.*
  FROM users a 
  --here I would need to join with payments table to get the columns from the payments table, 
  but where the user doesn't have a payment yet
  WHERE a.package = 1
  </code></pre>
  
  <p>The option to use the union doesn't seem like a good solution, but that's what I tried.</p>