MySQL NOT in Sub Query
<p>I'm working on a query and very stuck. I have distilled it down to what is below. </p>

<p>I'm getting data from feed where the user_id is not the current user, this case it is 1 and the privacy setting is less then 2. I how ever want to check if the current user has ever replied, in other words there is no row in the prfor table for the current user for that feed item. </p>

<p>At this point my logic has gone out the window and I can't for the life of me understand how it should work. Does it need a subquery at all for this sort of thing or can a join do it? </p>

<p>In the final query there are two JOIN's as well but that doesn't appear to be having an effect on the out come of what is below. </p>

<pre><code>SELECT
   pf.id, 
   pf.privacy
FROM 
   feed pf
WHERE 
   (
      pf.user_id != 1 
      AND pf.privacy &lt; 2
   ) 
   AND NOT (
      SELECT 
         pd.p_for 
      FROM 
         prfor pd 
      WHERE 
         pd.user_id = 1 
         AND pd.pfeed_id = pf.id 
         AND pd.p_for = 1 LIMIT 1
   )
</code></pre>

<p>Thanks for any insight that you can provide.</p>