title: removing rows from a SELECT based on columns in a different table
body: <p>I'm pretty much looking for a way to filter out rows from a <code>SELECT</code> of one table based on certain values in rows of another table.</p>
  
  <p>I'm experimenting with the example structure below. I've got a table of blog post content (one row per blog post), and another table of metadata about the posts (one row per key-value pair; each row with a column associating it with a blog post; many rows per blog post). I want to pull a row of <code>posts</code> only if there exists no rows in <code>metadata</code> where <code>metadata.pid=posts.pid AND metadata.k='optout'</code>. That is, for the example structure below, I just want to get back the <code>posts.id=1</code> row.</p>
  
  <p>(Based on what I've tried) <code>JOIN</code>s don't end up removing the posts which have some metadata where <code>metadata.k='optout'</code>, because the other row of metadata for that <code>pid</code> means it makes it into the results.</p>
  
  <pre><code>mysql&gt; select * from posts;
  +-----+-------+--------------+
  | pid | title | content      |
  +-----+-------+--------------+
  |   1 | Foo   | Some content |
  |   2 | Bar   | More content |
  |   3 | Baz   | Something    |
  +-----+-------+--------------+
  3 rows in set (0.00 sec)
  
  mysql&gt; select * from metadata;
  +------+-----+--------+-----------+
  | mdid | pid | k      | v         |
  +------+-----+--------+-----------+
  |    1 |   1 | date   | yesterday |
  |    2 |   1 | thumb  | img.jpg   |
  |    3 |   2 | date   | today     |
  |    4 |   2 | optout | true      |
  |    5 |   3 | date   | tomorrow  |
  |    6 |   3 | optout | true      |
  +------+-----+--------+-----------+
  6 rows in set (0.00 sec)
  </code></pre>
  
  <p>A subquery can give me the inverse of what I want:</p>
  
  <pre><code>mysql&gt; select posts.* from posts where pid = any (select pid from metadata where k = 'optout');
  +-----+-------+--------------+
  | pid | title | content      |
  +-----+-------+--------------+
  |   2 | Bar   | More content |
  |   3 | Baz   | Something    |
  +-----+-------+--------------+
  2 rows in set (0.00 sec)
  </code></pre>
  
  <p>...but using <code>pid != any (...)</code> gives me all 3 of the rows in posts, cause every single <code>pid</code> has a metadata row where <code>k!='optout'</code>.</p>