Search code examples
sqlselectcross-join

SQL for retrieving tuples of not related records

Imagine a database like this:

test database

And looking for all the students - teachers sets that don't share any courses. Result should be a (Student.Name - Teacher.Name) tuple.

This idea is near the solution but not exactly what wanted. 

--- The students that go to some courses 
SELECT S.FIRSTNAME, S.LASTNAME
FROM STUDENTS S
JOIN STU_COU SC ON S.STUDENTID = SC.STUDENTS_STUDENTID

UNION ALL

--- teachers not in attended courses 
SELECT T.FIRSTNAME, T.LASTNAME
FROM TEA_COU TC
JOIN TEACHERS T ON T.TEACHERID = TC.TEACHERS_TEACHERID
WHERE TC.COURSES_COURSEID NOT IN (
  SELECT C.COURSEID
  FROM STUDENTS S
  JOIN STU_COU SC ON S.STUDENTID = SC.STUDENTS_STUDENTID
  JOIN COURSES C ON C.COURSEID = SC.COURSES_COURSEID
);

Test data like:

  • TEACHER A teaches CS and MATH courses;
  • STUDENT A goes to CS course;
  • STUDENT B goes to LITERATURE and SPORTS courses;

Result would be 

STUDENT B - TEACHER A

Looking for universal solution that's why no particular database stated.

Solution

  • You could start with a cross join and then remove any pair that has a relationship:

    SELECT     s.firstname, s.lastname, t.firstname, t.lastname
FROM       students s
CROSS JOIN teachers t
WHERE      NOT EXISTS (SELECT *
                       FROM   stu_cou sc
                       JOIN   tea_cou tc ON sc.courses_courseid = 
                                            tc.courses_courseid
                       WHERE  sc.students_studentid = s.studentid AND
                              tc.teachers_teacherid = t.teacherid)