CODEWITHSUNDEEP
mysqlsql
Paul
Paul

Reputation: 326

MySQL NOT in Sub Query

I'm working on a query and very stuck. I have distilled it down to what is below.

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.

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?

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. 

SELECT
   pf.id, 
   pf.privacy
FROM 
   feed pf
WHERE 
   (
      pf.user_id != 1 
      AND pf.privacy < 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
   )

Thanks for any insight that you can provide.

Upvotes: 2

Views: 5284

Answers (3)

NAVAS MC
NAVAS MC

Reputation: 9

SELECT 
topt.* 
FROM fields_options AS topt
WHERE (topt.field_id=2)  AND topt.translation_of  
NOT IN  (SELECT top.translation_of FROM fields_options AS top WHERE top.field_id=2 AND top.translation_lang='ar' ) 
ORDER BY topt.translation_of

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You should use or not in for test if pf.id is not in prfor

    SELECT
    pf.id, pf.privacy
    FROM feed pf
    WHERE (pf.user_id != 1 AND pf.privacy < 2) AND  pf.user_id  NOT IN 
    (SELECT pf.id FROM prfor pd WHERE pd.user_id = 1 AND pd.pfeed_id = pf.id AND pd.p_for = 1 LIMIT 1)

or not exists

    You should use or not in  
    SELECT
    pf.id, pf.privacy
    FROM feed pf
    WHERE (pf.user_id != 1 AND pf.privacy < 2) AND   NOT EXIST
    (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)

Upvotes: 3

Giorgos Betsos
Giorgos Betsos

Reputation: 72225

You have to use NOT EXISTS:

SELECT pf.id, pf.privacy
FROM feed pf
WHERE (pf.user_id != 1 AND pf.privacy < 2) AND 
     NOT EXISTS (SELECT pd.p_for 
                 FROM prfor pd 
                 WHERE pd.pfeed_id = pf.id AND pd.user_id = 1 AND 
                       pd.p_for = 1)

Upvotes: 1

Related Questions