0

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.

VFX
  • 496
  • 4
  • 13
  • 1
    Do you want each student to have **exactly** those two courses, or _at least_ those two courses (but could have more) –  Dec 07 '20 at 07:49
  • 2
    Unrelated to your question, but shouldn't that be a many-to-many relationship? One course can contain multiple students and one student enrolls with multiple courses –  Dec 07 '20 at 07:50
  • At least these 2 courses – VFX Dec 07 '20 at 07:50
  • True, it is better to be many to many, but I just used this example to make the question simple – VFX Dec 07 '20 at 07:51

1 Answers1

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
S-Man
  • 22,521
  • 7
  • 40
  • 63
  • thanks for effort you put on the answer, it looks fine in the demo, but to make it work for my case I am trying to understand how the sum is being 2 when you are using OR, so I tried this ````select (true or true)::int as x ```` and it is giving me 1 as I expected. can you please explain how are you getting the sum 2? – VFX Dec 07 '20 at 08:49
  • You're right: The bool condition gives 0 or 1. Since `student _id == 2` (in my fiddle) matches this condition twice (first for the `math`, second for the `history` record), the sum of two matches is 2. So, if you need to add a third condition, the sum must be 3, because the table must meet the condition three times. I added the intermediate step here: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=b7f607ed9d2b35a389bb74635b1ecbcc – S-Man Dec 07 '20 at 08:55
  • Thanks for the explanation and the elegant solution – VFX Dec 07 '20 at 09:04