CODEWITHSUNDEEP
mysql
Rob Cole
Rob Cole

Reputation: 721

Select MYSQL records where child record in a set is missing

Here's the situation: If someone takes one course, they have to take a set of other courses as a supplement. How can I identify those courses a student has not yet taken?

Here are my tables:

tbl_course_dependency_lookup
courseid    dependentid
133          57
133          55
133          71
167          57
167          99

tbl_user_course_completed
userid      courseid
 12         133
 12          55
 13          71
 14         133
 15         100

Here is the data that should be returned:

userid      courseid      dependentid
12          133           57
12          133           71
14          133           55
14          133           57
14          133           71

Upvotes: 0

Views: 45

Answers (1)

Strawberry
Strawberry

Reputation: 33945

 DROP TABLE IF EXISTS course_dependency;

 CREATE TABLE course_dependency
 (course_id    INT NOT NULL
 ,dependent_id INT NOT NULL
 ,PRIMARY KEY(course_id,dependent_id)
 );

 INSERT INTO course_dependency VALUES
 (133          ,57),
 (133          ,55),
 (133          ,71),
 (167          ,57),
 (167          ,99);

 DROP TABLE IF EXISTS user_course;

 CREATE TABLE user_course
 (user_id INT NOT NULL
 ,course_id INT NOT NULL
 ,PRIMARY KEY(user_id,course_id)
 );

 INSERT INTO user_course VALUES
 (12         ,133),
 (12          ,55),
 (13          ,71),
 (14         ,133),
 (15         ,100);

 SELECT uc.* 
      , cd.dependent_id 
   FROM user_course uc 
   JOIN course_dependency cd 
     ON cd.course_id = uc.course_id 
   LEFT 
   JOIN user_course ucx 
     ON ucx.user_id = uc.user_id 
    AND ucx.course_id = cd.dependent_id 
  WHERE ucx.user_id IS NULL;

 +---------+-----------+--------------+
 | user_id | course_id | dependent_id |
 +---------+-----------+--------------+
 |      12 |       133 |           57 |
 |      12 |       133 |           71 |
 |      14 |       133 |           55 |
 |      14 |       133 |           57 |
 |      14 |       133 |           71 |
 +---------+-----------+--------------+

Upvotes: 1

Related Questions