CODEWITHSUNDEEP
sqloracle-database
Georgi Georgiev
Georgi Georgiev

Reputation: 1623

Difficult oracle query

I have a database which looks like this:

table_Students: { studentid(PK), name };

table_Stu_cou:{ studentid(FK), courseid(FK) };

table_Courses:{ courseid(PK), coursename };

table_Tea_cou { courseid(FK), teacherid(FK) };

table_Teachers:{ teacherid(PK), name};

The stu_cou table shows which students attend which courses. The tea_cou tables shows which teachers teach which course. I have to list all students and teachers who have never met (the student has never attended a course presented by this lecturer). But I can't figure out how to make it and I've been trying for 2 days. Could you help me? I'm using Oracle.

Upvotes: 0

Views: 132

Answers (3)

Patashu
Patashu

Reputation: 21793

SELECT s.name, t.name FROM students s CROSS JOIN teachers t
WHERE NOT EXISTS (
    SELECT 1 FROM courses c
    JOIN stu_cou sc ON sc.courseid = c.courseid AND sc.studentid = s.studentid
    JOIN tea_cou tc ON tc.courseic = c.courseic AND tc.teacherid = t.id
)

Basically, for every possible combination of student and teacher, is there a course that has been attended by that student and teached by that teacher?

Upvotes: 1

Jens Schauder
Jens Schauder

Reputation: 81988

This should do the trick, once you fixed my typos

select t.name, s.name
from  table_Teachers t, table_Students s
where not exists (
    select 'x' 
    from table_Stu_cou sc, table_Tea_cou tc
    where sc.courseid = tc.courseid
    and sc.studentid = s.studentid
    and tc.teacherid = t.teacherid
)

Upvotes: 0

dmg
dmg

Reputation: 4491

What you need is to first compute all the potential pairs student,teacher, then subtract the student teachers that have met:

The first is done with a crossproduct of students and teachers. The second with a join based on the courses have taken:

SELECT studentid, teacherid from students, teachers

EXCEPT

select studentid, teacherid from stu_cou natural join tea_cou;

If you are interested in student names and teacher names you can use this result as a subquery and do joins to the Students and Teachers tables to get that information. But I will leave that as an exercise for you to do.

--dmg

Upvotes: 0

Related Questions