Select MYSQL records where child record in a set is missing -

Select MYSQL records where child record in a set is missing -

here's situation: if takes 1 course, have take set of other courses supplement. how can identify courses pupil has not yet taken?

here 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 info should returned:

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

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 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 user_course values (12 ,133), (12 ,55), (13 ,71), (14 ,133), (15 ,100); select uc.* , cd.dependent_id user_course uc bring together course_dependency cd on cd.course_id = uc.course_id left bring together user_course ucx on ucx.user_id = uc.user_id , ucx.course_id = cd.dependent_id ucx.user_id null; +---------+-----------+--------------+ | user_id | course_id | dependent_id | +---------+-----------+--------------+ | 12 | 133 | 57 | | 12 | 133 | 71 | | 14 | 133 | 55 | | 14 | 133 | 57 | | 14 | 133 | 71 | +---------+-----------+--------------+

mysql