CODEWITHSUNDEEP
sqlsqlplus
The Rationalist
The Rationalist

Reputation: 753

Need to find all tuples in two relations that do not participate in their natural join

Here is the schema:

Student-schema =(studentnum, name, standing, gpa, major)
Class-schema = (schedulenum, semester, department, classnum, days, time, place, enrollment)
Instructor-schema = (name, department, office)
Teaches-schema = (name, schedulenum, semester)
Taking-schema = (studentnum, schedulenum, semester, grade)

I have to write a query that finds all tuples in student and taking that don't participate in the natural join of student and taking. The result is a single relation with tuples from both student and taking that are padded with nulls appropriately.

The only idea I can come up with is to get the full join of student/taking and then keep the tuples that are left over after removing the natural join tuples. But I'm not sure how to go about this. Here's the best solution I've come up with so far, but I'm getting no rows selected. I'm pretty sure I should have some results. I'm just looking for any suggestions that a SQL pro might be willing to share.

SELECT*
FROM student full join taking ON student.studentnum = taking.studentnum
MINUS
SELECT*
FROM student,taking
WHERE student.studentnum = taking.studentnum;

Upvotes: 0

Views: 283

Answers (1)

Majid Darabi
Majid Darabi

Reputation: 741

This sql code works in Oracle! you should replace full outer join and natural join in order to work in your DBMS.

( SELECT * FROM student NATURAL FULL OUTER JOIN taking ) MINUS ( SELECT * FROM student NATURAL INNER JOIN taking )

Upvotes: 1

Related Questions