Good day! Faced the following task: it is necessary to select all students who have completed all the specified courses. He wrote his guesses below, but this request selects those students who have passed at least one of the indicated ones.

SELECT student.Id, student.FirstName, student.LastName, student.MiddleName FROM student INNER JOIN coursegroupe AS cg ON cg.IdCourse IN ( '045d4fdd-83ac-45d5-9c7b-cd8e9ce85bf6','e2b59d3c-9c1e-44f5-aa9c-190ce8f063b2') INNER JOIN groupestudent AS gs ON gs.IdGroupe = cg.Id WHERE student.Id = gs.IdStudent;
  • I think your question will be resolved faster if you bring in the structure of your base and a few words about each table - therainycat

1 answer 1

The simplest solution that came to mind is to leave your request and impose a condition on the group.

 SELECT student.Id, student.FirstName, student.LastName, student.MiddleName FROM student INNER JOIN coursegroupe AS cg ON cg.IdCourse IN ( '045d4fdd-83ac-45d5-9c7b-cd8e9ce85bf6','e2b59d3c-9c1e-44f5-aa9c-190ce8f063b2') INNER JOIN groupestudent AS gs ON gs.IdGroupe = cg.Id WHERE student.Id = gs.IdStudent; GROUP BY student.id HAVING COUNT(cg.idCourse)=2

In general, the point is that we do the JOIN and check that the number of courses completed from the data of the attached data is two (HAVING COUNT (cg.idCourse) = 2) naturally using grouping (GROUP BY student.id)

  • coursegroupe there is no student field, the link to the student is contained in the groupestudent. - Igor Klimenko
  • Yes, sorry, I did not notice :) I did not see the structure of your database :) - Firepro
  • Yes, you excuse me for not throwing it off) - Igor Klimenko