CODEWITHSUNDEEP
mysqljoinleft-join
Avi
Avi

Reputation: 149

Mysql join, negative condition to exlude results

I have a user table and an item table where I can assign multiple items to each user. I need a simple query, that will select all users who don't have assigned items with certain type.

The problem with my query is that if some user have assigned multiple items, let's say 100, 200 and 300, they will still be selected, because they have assigned items with types 100 and 200. What I want is to completly exclude all users who have assigned item 300, regardless from any other items they may have assigned.

I found similar question with answers but it deals with a different relation type (MtM) and I have a problem with translating answers to my particular case.

My incomplete query that needs expanding

SELECT * FROM user
LEFT JOIN item
ON user.id = item.assigned_to_id 
WHERE item.id is null OR item.type != 300

Properly working query but with a subquery, which I would like to avoid

SELECT * FROM user
WHERE user.id NOT IN 
(
SELECT i.assigned_to_id 
FROM item i 
WHERE i.type = 300 
AND i.assigned_to_id is not null
)

Upvotes: 0

Views: 156

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Simply put the conditions of you subquery to the Left Join condition and add where clause for checking null in id field of item table.

You can write the equivalent query using left join like below.

select
u.*
from user u
left join item i on u.id=i.assigned_to_id and i.type=300
where i.id is null

Upvotes: 1

Related Questions