title: MySQL - How to combine 2 tables in a query when one table may not have a corresponding row
body: <p>I am writing a basic CMS system with NodeJS and MySQL.  I am using the tables structure for posts that WordPress also uses, that being 'posts' and 'post_meta'.</p>
  
  <p>The 'posts' table has the following columns...</p>
  
  <pre><code>id
  post_title
  post_date
  ...etc
  </code></pre>
  
  <p>The 'post_meta' table has the following columns...</p>
  
  <pre><code>id
  post_id - ID of associated post
  meta_key
  meta_value
  </code></pre>
  
  <p>I like this style as it keeps my rows easy to read in a SQL editor without having to scroll as long lengths, as well as add additional meta data as needed.</p>
  
  <p>Using JOINs (Query below) I am able to make a result set that contains the values from 'posts', as well as additional columns named after 'post_meta.meta_key' that contain the value of 'post_meta.meta_value'.</p>
  
  <p>The problem I am currently running into is how I would go about returning results where a 'meta_key' is not present in the 'posts_meta' table, and instead filling that column of the result with NULL.</p>
  
  <p>Example usage.  2 Posts are in the 'posts' table, the first has 2 meta_keys associated with it, the other has only 1.</p>
  
  <p><strong>'posts' table contents</strong></p>
  
  <pre><code>id : 1
  title : 'Post 1'
  post_date : 'timestamp of posted date/time'
  
  id : 2
  title : 'Post 2'
  post_date : 'timestamp of posted date/time'
  </code></pre>
  
  <p><strong>'posts_meta' table contents</strong></p>
  
  <pre><code>id : 1
  post_id : 1
  meta_key : 'key1'
  meta_value : 'key1 value'
  
  id : 2
  post_id : 1
  meta_key : 'key2'
  meta_value : 'key2 value'
  
  id : 3
  post_id : 2
  meta_key : 'key1'
  posts_meta.meta_value : 'key1 value'
  </code></pre>
  
  <p>the results I would like to get from this are</p>
  
  <pre><code>post_id : 1
  title : 'Post 1'
  post_date : 'timestamp of posted date/time'
  key1 : 'key1 value'
  key2 : 'key2 value'
  
  post_id : 2
  title : 'Post 2'
  post_date : 'timestamp of posted date/time'
  key1 : 'key1 value'
  key2 : NULL
  </code></pre>
  
  <p>However, with the code I am currently working with, the second result (Post 2) does not return as part of the results, only 'Post 1', since all of the meta_keys that were being tested for exist in the 'post_meta' table.</p>
  
  <p>Here is my SQL code as it currently stands (edited to keep to the example above)</p>
  
  <pre><code>SELECT
      posts.id,
      posts.title,
      posts.post_date,
      m1.meta_value as `key1`,
      m2.meta_value as `key2`
  FROM posts
  LEFT JOIN post_meta as m1 ON ( posts.id = m1.post_id )
  LEFT JOIN post_meta as m2 ON ( posts.id = m2.post_id )
  WHERE
      ( m1.meta_key = 'key1' )
      AND ( m2.meta_key = 'key2' )
  ORDER BY posts.post_date DESC
  </code></pre>
  
  <p>as you can see, i am selecting the meta_values from the 2 JOINs, and naming them after the specific key each of those joins is being used to retrieve (as defined in the WHERE clause).</p>
  
  <p>I know it has got to be something simple, since I am almost there.  It returns rows that have both key1 and key2, but not rows that are completely missing either or all of them.  I want it to return all rows, and if a key is missing, fill that field in the row with NULL</p>