In SQL I have three tables as follows: table name = users, id is primary key and of type int, name is of type text

In SQL I have three tables as follows: table name = users, id is primary key and of type int, name is of type text

table name = courses, id is primary key and of type int, name is of type text and userid is a foreign key for the ids in table users and is of type int

table name = modules, id is primary key and of type int, name is of type text, userid is the foreign key for the ids in the users table, and courseid is another foreign key for the ids in the courses table

I am trying to build an sql query that can return the rows in the modules table where the userid and courseid contain values that should not normally result from the primary ids/foreign keys shown in the courses table (I am trying to solve a potential corruption of data).

In the example above, I should return the rows of the modules table with primary ids 2 and 4, since the courses table has no rows where the foreign key (the user) is 1 and the primary id (course) is 3, and there are also no rows where the foreign key (the user) is 1 and the primary id (course) is 4. Thus the output should be:

I am not sure how to use union/intersection along with potential not and in to solve this - any guidance would be appreciated.

• sql
• mysql
• union

• I have made the suggested edits. I know the way the modules table has two foreign keys is strange but it's how I've already set up the table and in this specific scenario, having both foreign keys in the table is a part of solving this. – Gee Ji Commented Mar 10 at 15:47
• This doesn't seem to be a properly normalized database. The courses table shouldn't have userid in it (how do you represent a course that no one has signed up for yet?), there should be a separate table that lists the students in each course. – Barmar Commented Mar 10 at 16:54

1 Answer 1

I'm not sure why you think you want UNION or INTERSECT, because EXCEPT would be the correct set operator.

SELECT m.courseid, m.userid
FROM modules m
EXCEPT
SELECT c.id, c.userid
FROM courses c;

But you probably want a NOT EXISTS rather than EXCEPT, as this allows you to show all columns. Do not use NOT IN as this has weird effects in the presence of nulls.

SELECT m.*
FROM modules m
WHERE NOT EXISTS (SELECT 1
    FROM courses c
    WHERE c.id = m.courseid
      AND c.userid = m.userid);

db<>fiddle

Having said that, your table design is heavily denormalized.

• courses should be split into two separate tables

courses

user_courses

• And modules should also be split.

modules

This needs a secondary unique key (courseid, id) for the foreign key below.

user_modules

This last table should be foreign-keyed like this:

FOREIGN KEY (courseid, userid) REFERENCES user_courses (courseid, userid),
FOREIGN KEY (courseid, moduleid) REFERENCES modules (courseid, id)

Note that many-to-many join tables should not normally have a separate primary key. The PK is made up of the columns of the FKs.