Postgresql: One-To-Many Relationship With Multiple Conditions On The Many Side

Question

I have a Students table that has the following columns

id
student_name

And Courses table

id
student_id  //a foreign key
course_name
credits

It is a one to many relationship(one student might have multiple courses).

I want to list all the students that have these 2 courses

first course:   course_name -> math    , credit -> 5
second course:  course_name -> history , credit -> 3

Please note that each student has to have at least these two courses.

I can get what I want by joining with the Courses table twice(once for the first course and another for the second course), but in case I want to add one more course to the condition I will need to join one more time.

So can you please guide me to another approach.

Answer 1

Does this solve your problem? I count all occurrences for your expected courses and the sum must be 2.

demo:db<>fiddle

SELECT
    s.id
FROM students s
JOIN courses c
ON c.student_id = s.id
GROUP BY s.id
HAVING SUM(
    ((c.course_name = 'math' AND c.credits = 5)
    OR
    (c.course_name = 'history' AND c.credits = 3))::int
) = 2

Alternative to the SUM(condition::int) you could use the COUNT() with a FILTER clause:

demo:db<>fiddle

HAVING COUNT(*) FILTER (WHERE
    (c.course_name = 'math' AND c.credits = 5)
    OR
    (c.course_name = 'history' AND c.credits = 3)
) = 2