MySQL query with WHERE NOT EXISTS returning no rows

Question

I have 2 tables, one called course and another called course_tutor_link. I am trying to get a list of all the courses that a tutor is not subscribed to. This would be a simple left join but because at the start I will have courses that have no tutors attached to them this seems to mess up the code, so far I have:

SELECT  c.course_id, c.course,
users_id
FROM course AS c
LEFT JOIN course_tutor_link AS ctl
USING (course_id)
GROUP BY course_id

This will give me all of the courses including the ones that the user is subscribed to (about 11 rows), as soon as I try and do a filter with the users_id I automatically also lose all of the courses that have no tutor attached eg.

SELECT c.course_id, c.course 
FROM course AS c
JOIN course_tutor_link AS ctl
USING (course_id)
WHERE users_id !=9

It drops down to just the courses I have tutors subscribed to. looking through SO I noticed the use of sub-queries and not exist but I just can't get the correct syntax, all I get is no rows returned, the query I have is:

SELECT  c.course_id, c.course,
users_id
FROM course AS c
LEFT JOIN course_tutor_link AS ctl
USING (course_id)
WHERE NOT EXISTS (SELECT course_id FROM course_tutor_link WHERE users_id = 9)

I'm obviously doing something stupid but I don't know what

regards

Zen

Your second code sample seems to be identical to the first. — PM 77-1
– PM 77-1, Commented Dec 30, 2013 at 0:25

toph · Accepted Answer · 2013-12-30 00:23:47Z

1

SELECT c.course_id, c.course
FROM course AS c
    LEFT JOIN course_tutor_link AS ctl 
        ON c.course_id=ctl.course_id AND ctl.user_id=9
WHERE ctl.course_id IS NULL

answered Dec 30, 2013 at 0:23

toph

2202 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Zenonline Over a year ago

Doh! talk about a brain fart. It works perfectly thanks toph and as I understand it more efficient than the subquery method that I was asking for and you have given below?, I completely forgot about the obvious IS NULL and had tried course_is=NULL which doesn't work

toph Over a year ago

My two answers are correct. The better one depends of your data.You should try both against filled tables and choose the faster one.

toph · Accepted Answer · 2013-12-30 00:26:47Z

0

or

SELECT course_id, course
FROM course
WHERE course_id NOT IN (SELECT course_id FROM course_tutor_link WHERE users_id=9)

answered Dec 30, 2013 at 0:26

toph

2202 silver badges4 bronze badges

1 Comment

PM 77-1 Over a year ago

I believe it will be better if in the future you add 2nd (3rd, etc.) solution(s) to the same answer (edit) instead of creating new ones.

Collectives™ on Stack Overflow

MySQL query with WHERE NOT EXISTS returning no rows

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related