CODEWITHSUNDEEP
mysql
theisenm85
theisenm85

Reputation: 31

MySQL NOT IN query only including result that I want to exclude

Query:

SELECT * 
from t1
INNER JOIN t2 ON t1.item_id = t2.item_id
WHERE t1.item_id NOT IN
(Select t2.item_id from t2)

Basically, I'm trying to exclude all results with an item_id in the t2 table, but it is only including results with an item_id in the t2 table. What am I missing here....

Thanks for any and all responses!

Upvotes: 0

Views: 54

Answers (3)

Abe Miessler
Abe Miessler

Reputation: 85126

I'm surprised you are getting anything with that query since you seem to be excluding anything you would have included through the join. Try changing your query to this:

SELECT * from t1
WHERE item_id NOT IN (SELECT item_id from t2)

Upvotes: 2

Andrius Naruševičius
Andrius Naruševičius

Reputation: 8578

You join and then exclude everything :/

At least that's what sql says here

Try something like this:

SELECT 
    item_id
FROM 
    t1
WHERE 
    item_id NOT IN (SELECT item_id from t2)

as in here.

Upvotes: 0

colonelclick
colonelclick

Reputation: 2215

This will select items from T1 that do NOT have a corresponding entry in T2. I think this is what you wanted.

SELECT * 
from t1
LEFT JOIN t2 ON t1.item_id = t2.item_id
WHERE t2.item_id IS NULL

Upvotes: 0

Related Questions