Return results from 3 tables where the number of results should be equal to the number of rows in table 1 multiplied by the number of rows in table 3

Question

I have a PostgreSQL database with three tables:

• user: A list of users of the application. Primary key is user_id.
• course: A catalog of courses that users can enroll into. Primary key is course_id.
• enrollment: Records of the users' enrollment into courses. Primary key is enrollment_id.

Relations between the tables:

• The enrollment table has two foreign keys that can not be null:

  • enrollment.user_id references user.user_id.
  • enrollment.course_id references course.course_id.

• If user1 is enrolled in course1, there should be only one record in the enrollment table that can be used to join user1 to course1.

• If user1 is not enrolled in course1, no record exists in the enrollment table to join user1 to course1.

The problem:

• I can not find out how to write a database view which, if I have 2 users and 3 courses, returns 6 rows that look like this:

user_id | course_id | enrollment_id
-------------------------------------
user1   | course1   | enrollment id of user1 for course1 or null
user1   | course2   | enrollment id of user1 for course2 or null
user1   | course3   | enrollment id of user1 for course3 or null
user2   | course1   | enrollment id of user2 for course1 or null
user2   | course2   | enrollment id of user2 for course2 or null
user2   | course3   | enrollment id of user2 for course3 or null

Some context:

• I can not change anything about the database.
• I am not concerned about performance.

My work in progress:

I candidly hoped that this would work:

select user.user_id, course.course_id, enrollment.enrollment_id
from user
full outer join enrollment on enrollment.user_id = user.user_id
full outer join course on course.course_id = enrollment.course_id
 

This query does not return the results I'm expecting due to the fact that there isn't an enrollment record for courses the users are not enrolled into. The results, for example, look more like this:

user_id | course_id | enrollment_id
-------------------------------------
user1   | null      | null
user2   | course1   | enrollment id of user2 for course1
user2   | course2   | enrollment id of user2 for course2

Which is not what I expect, as I wish the table to return 3 records for user1, who is not enrolled in any course, instead of only one row where course_id and enrollment_id are null. Similarly, a third record for user2 is missing since they are not enrolled in course3, which is not what I'm hoping for.

How can I solve this problem?

Answer 1

cross join users & courses, left join enrollment:

select u.user_id, c.course_id, e.enrollment_id
from users u
cross join courses c
left join enrollment e
    on e.user_id = u.user_id
    and e.course_id = c.course_id

dbfiddle here (Postgres 14)