CODEWITHSUNDEEP
sqlpostgresql
Michael Victor
Michael Victor

Reputation: 891

Opposite of UNION SQL Query

I have 2 tables : 

interests (storing the interest ID and name)
person_interests(storing the person_id and interest_id)

How do I select all the interests that a particular person has not selected?

I have tried the following SQL Query and am still not getting the desired result 

SELECT * 
  FROM interests LEFT JOIN person_interests 
               ON interests.id=person_interests.person_id
 WHERE person_interests.id IS NULL 
   AND person_id=66;

Upvotes: 1

Views: 3740

Answers (3)

MWeber
MWeber

Reputation: 613

There are a couple things going on.

First, I think you have an error in your join. Shouldn't it be interests.id=person_interests.interest_id instead of interests.id=person_interests.person_id?

That aside, I still don't think you would be getting the desired result because your person_id filter is on the RIGHT side of your LEFT OUTER join, thus turning it back into an inner join. There are several ways to solve this. Here's what I would probably do:

SELECT * 
FROM 
  (SELECT interests.*, person_id 
  FROM interests LEFT JOIN person_interests 
       ON interests.id=person_interests.interest_id
   WHERE person_interests.id IS NULL )
WHERE person_id=66;

Upvotes: 0

SoulTrain
SoulTrain

Reputation: 1904

Use NOT EXISTS

SELECT *
FROM interests
WHERE NOT EXISTS (
        SELECT person_interests.interest_id
        FROM person_interests
        WHERE person_id = 66
            AND interests.id = person_interests.interest_id
        )

Upvotes: 5

Oferch
Oferch

Reputation: 71

SELECT * from interests  
WHERE interest_id NOT IN  
(SELECT interest_id FROM person_interests WHERE person_id=66)

Upvotes: 0

Related Questions