Mysql join, negative condition to exlude results

Question

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
)

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code in table format. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 15 '20 at 03:35
This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. Reflect your research. See [ask] & the voting arrow mouseover texts. If you post a question, use one phrasing as title. — philipxy, Oct 15 '20 at 03:35
Does this answer your question? [SQL - find records from one table which don't exist in another](https://stackoverflow.com/questions/367863/sql-find-records-from-one-table-which-dont-exist-in-another) — Ken White, Oct 15 '20 at 03:57
@AkhileshMishra It very quickly becomes difficult to read material where code font is used for non-code, ie words/names that happen to appear in code that aren't being code while being read, but merely name things. Moreover using or nt using this style is entirely a matter of taste, so editing other people's posts to do this is inappropriate; if one such edit is reasonable then every post could constantly be being edited back & forth between styles. So please keep your style choice to your own posts, and keep posts in their own style. Use code font for code. — philipxy, Oct 15 '20 at 05:47
And never use evil `SELECT *`. Instead, name and qualify only the columns you actually want returned. — Strawberry, Oct 15 '20 at 07:12

score 1 · Accepted Answer · answered Oct 15 '20 at 05:35

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

Mysql join, negative condition to exlude results

1 Answers1