Converting NOT IN with WHERE to join

Question

I am trying to fetch all record that are not related to a certain member, registration is an intersection entity that solves the many to many for members to lessons.

SELECT * FROM lesson
INNER JOIN training
ON training.id = lesson.training_id
WHERE lesson.id
NOT IN (SELECT registration.lesson_id FROM registration WHERE registration.member_id = 42)

I have been trying to convert this code to use a join statement but for I just can't get it to work.

SELECT  l.*
FROM    t_left l
LEFT JOIN
t_right r
ON      r.value = l.value
WHERE   r.value IS NULL

this technique doesn't work since this returns record of lesson that don't have any relation ship at all, I need it to look if it doesn't have a relationship to a specific member.

entity relationships model

This is unclear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. When it is clear it will likely be a faq. Before considering posting please always 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. — philipxy, Jan 10 '20 at 11:39
Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give 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, which includes constraints & indexes & tabular initialization. PS See [ask] & the voting arrow mouseover texts. PS Beware of how IN & NOT in treat NULLs; prefer (NOT) EXISTS. — philipxy, Jan 10 '20 at 11:41
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) — philipxy, Jan 10 '20 at 11:46
the link you sent did not provide an answer. As I said `this technique doesn't work since this returns record of lesson that don't have any relation ship at all, I need it to look if it doesn't have a relationship to a specific member.` — Dylan, Jan 10 '20 at 13:29
And I said your post is not clear, so it is pointless to repeat it. PS I also said, cut & paste & runnable code. PS Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Jan 10 '20 at 13:42

GMB · Answer 1 · 2020-01-10T11:41:53.150

1

I think that you just need to add a join condition on the member that you are looking for:

SELECT l.*
FROM t_left l
LEFT JOIN t_right r ON r.value = l.value and r.member_id = 42
WHERE r.value IS NULL

Or, starting from your other query:

SELECT l.*, t.* 
FROM lesson l
INNER JOIN training t ON t.id = l.training_id
INNER JOIN registration r ON r.lesson_id = l.id AND r.member_id = 42
WHERE r.lesson_id IS NULL

edited Jan 10 '20 at 11:41

answered Jan 10 '20 at 11:33

GMB

216,147
25
84
135

My sincere thanks, this seems to work for me. I can't thank you enough since I've been struggeling with this for so long but your solution makes perfect sense. – Dylan Jan 10 '20 at 11:40

score 1 · Answer 2 · answered Jan 10 '20 at 11:41

1

You want to turn your NOT IN into an anti join? Why? I find NOT IN much more readable.

Anyway:

SELECT * 
FROM lesson l
INNER JOIN training t ON t.id = l.training_id
LEFT JOIN registration r ON r.lesson_id = l.id AND r.member_id = 42
WHERE r.id IS NULL;

answered Jan 10 '20 at 11:41

Thorsten Kettner

89,309
7
49
73

score 0 · Answer 3 · answered Jan 10 '20 at 11:40

The conversion from NOT IN or NOT EXISTs uses LEFT JOIN and a comparison to indicate that the JOIN failed.

That would be:

SELECT *
FROM lesson l INNER JOIN
     training t
     ON t.id = l.training_id LEFT JOIN
     registrations r
     ON r.lesson_id = l.id AND r.member_id = 42
WHERE r.lesson_id IS NULL;

Very important: The comparison on member_id needs to go in the ON clause, not the WHERE clause.

Converting NOT IN with WHERE to join

3 Answers3