title: Query with NOT IN subquery returning 0 rows
body: <p>I am having issues with a SQL query that ideally should return all the comments to a thread in a forum.</p>
  
  <p>Right now i'm having the following query:</p>
  
  <pre><code>    SELECT p.*, 'BBCode' AS Format,
          FROM_UNIXTIME(TIME) AS DateInserted,
          FROM_UNIXTIME(editTime) AS DateUpdated
      FROM et_post p
      LEFT JOIN et_conversation c ON c.conversationId = p.conversationId
      WHERE c.private = 0
      AND p.postId NOT IN (
          SELECT p.postId
          FROM et_conversation c
          LEFT JOIN et_post p ON p.conversationId = c.conversationId WHERE c.private = 0
          GROUP BY p.conversationId
          ORDER BY p.TIME
      )
  </code></pre>
  
  <p>This, however, returns 0 rows. I expect it to return around 8800 rows.</p>
  
  <p><strong>If I run the first part alone:</strong></p>
  
  <pre><code>    SELECT p.*, 'BBCode' AS Format,
          FROM_UNIXTIME(TIME) AS DateInserted,
          FROM_UNIXTIME(editTime) AS DateUpdated
      FROM et_post p
      LEFT JOIN et_conversation c ON c.conversationId = p.conversationId
      WHERE c.private = 0
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code># postId, conversationId, memberId, time, editMemberId, editTime, deleteMemberId, deleteTime, title, content, attributes, Format, DateInserted, DateUpdated
  '12', '5', '1', '1436600657', NULL, NULL, NULL, NULL, '', 'Content1', ?, 'BBCode', '2015-07-11 09:44:17', NULL
  '13', '5', '1', '1436600681', NULL, NULL, NULL, NULL, 'Testing area', 'Content2', ?, 'BBCode', '2015-07-11 09:44:41', NULL
  '14', '5', '1', '1436600698', NULL, NULL, NULL, NULL, 'Testing area', 'Content 3', ?, 'BBCode', '2015-07-11 09:44:58', NULL
  '15', '5', '19', '1436602065', NULL, NULL, NULL, NULL, 'Testing area', 'More content', ?, 'BBCode', '2015-07-11 10:07:45', NULL
  '16', '5', '19', '1436602093', NULL, NULL, NULL, NULL, 'Testing area', 'Even more content', ?, 'BBCode', '2015-07-11 10:08:13', NULL
  '17', '5', '1', '1436602137', NULL, NULL, NULL, NULL, 'Testing area', 'Will it ever stop?', ?, 'BBCode', '2015-07-11 10:08:57', NULL
  '54', '5', '1', '1436617274', NULL, NULL, NULL, NULL, 'Testing area', 'Ah, final one..', ?, 'BBCode', '2015-07-11 14:21:14', NULL
  </code></pre>
  
  <p>It returns 9304 rows like the above which sounds right.</p>
  
  <p><strong>Running the subquery alone:</strong></p>
  
  <pre><code>        SELECT p.postId
          FROM et_conversation c
          LEFT JOIN et_post p ON p.conversationId = c.conversationId WHERE c.private = 0
          GROUP BY p.conversationId
          ORDER BY p.TIME
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code># postId
  '12'
  '18'
  '19'
  '44'
  '70'
  '73'
  '75'
  </code></pre>
  
  <p>And it gives me 412 rows like the above which also sounds right.</p>
  
  <p>Ideally, my output of the final query should look like this:</p>
  
  <pre><code># postId, conversationId, memberId, time, editMemberId, editTime, deleteMemberId, deleteTime, title, content, attributes, Format, DateInserted, DateUpdated
  '13', '5', '1', '1436600681', NULL, NULL, NULL, NULL, 'Testing area', 'Content2', ?, 'BBCode', '2015-07-11 09:44:41', NULL
  '14', '5', '1', '1436600698', NULL, NULL, NULL, NULL, 'Testing area', 'Content 3', ?, 'BBCode', '2015-07-11 09:44:58', NULL
  '15', '5', '19', '1436602065', NULL, NULL, NULL, NULL, 'Testing area', 'More content', ?, 'BBCode', '2015-07-11 10:07:45', NULL
  '16', '5', '19', '1436602093', NULL, NULL, NULL, NULL, 'Testing area', 'Even more content', ?, 'BBCode', '2015-07-11 10:08:13', NULL
  '17', '5', '1', '1436602137', NULL, NULL, NULL, NULL, 'Testing area', 'Will it ever stop?', ?, 'BBCode', '2015-07-11 10:08:57', NULL
  '54', '5', '1', '1436617274', NULL, NULL, NULL, NULL, 'Testing area', 'Ah, final one..', ?, 'BBCode', '2015-07-11 14:21:14', NULL
  </code></pre>
  
  <p>(Notice postId 12 is gone)</p>
  
  <p>[EDIT]
  From some quick head calculations I came up to the fact that the following query sounds right according to the number of rows returned:</p>
  
  <pre><code>    SELECT p.*, 'BBCode' AS Format,
          FROM_UNIXTIME(TIME) AS DateInserted,
          FROM_UNIXTIME(editTime) AS DateUpdated
      FROM et_post p
      INNER JOIN et_conversation c ON c.conversationId = p.conversationId
      WHERE c.private = 1
      AND p.postId NOT IN (
          SELECT DISTINCT po.conversationId
          FROM et_post po
      );
  </code></pre>
  
  <p>[EDIT2]
  Now with an <a href="http://sqlfiddle.com/#!9/e6f16/1" rel="nofollow">sqlfiddle</a></p>
  
  <p>Basically, I want the rows with id 12, 15 and 18 to be gone since they are the original posts created by the one who started the conversation. </p>
  
  <p>[EDIT3]
  Now with an <a href="http://sqlfiddle.com/#!9/ab144" rel="nofollow">updated sqlfiddle</a></p>
  
  <ul>
  <li>I dug some more into the database and figured out the first sqlfiddle wasn't 100% correct regarding how the data is in the database - therefore this updated version.</li>
  </ul>