Question 179682 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL How to return record ID's not included in table 2 from table 1 based off of user ID in table 2

I have two tables, one has course name and course ID. The second table has the ID of the students and the course ID they have taken. I need to find all the class ID’s of the classes a student hasn’t taken. For example, in table 2 student 03 has taken classes 01 and 02 but not 03 and 04 from table one. The course ID’s 03 and 04 from table one are what I need to return (all the classes student 03 hasn’t taken). I’ve tried numerous queries and the last one I tried is:

SELECT table1.* FROM table1
LEFT JOIN table2
ON
    table1.course_ID = table2.course_ID
WHERE
    table2.course_ID IS NULL
AND 
    table2.user_ID != 3

Appreciate your help!

table 1

course_ID courseName
01 math
02 English
03 art
04 music

table 2

cert_Id course_ID user_ID
01 01 03
02 02 03
Tags:

2

Answers


  1. 0 
    select t1.* from table1 as t1
left join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3 
where t2.user_ID is null;

    For more readable query, you can use EXCEPT if available:

    select * from table1
except
select t1.* from table1 as t1
inner join table2 as t2 on t2.course_ID = t1.course_ID and t2.user_ID = 3;
    Login or Signup to reply.
  2. 0

    As per your current requirement below query will work

    SELECT * FROM table1 t1 WHERE course_ID NOT IN (SELECT course_ID FROM table2 WHERE user_ID =3)

    If you have more records in table2 and if you need to populate more than one student details then you have to use other logic

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search