CODEWITHSUNDEEP
mysqlsql
Zenonline
Zenonline

Reputation: 102

MySQL query with WHERE NOT EXISTS returning no rows

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

Upvotes: 1

Views: 152

Answers (2)

toph
toph

Reputation: 220

or

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

Upvotes: 0

toph
toph

Reputation: 220

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

Upvotes: 1

Related Questions