CODEWITHSUNDEEP
sqldatabasesubquery
Jamie v Achteren
Jamie v Achteren

Reputation: 3

Check if multiple values in table 1 are defined in table 2 with multiple values

There are two tables: CourseModules and Students.

A student can participate in different modules. If a student succeed the module, it will be stored in the database under StudentsModule with the moduleID.

The table courseModules defines all required modules for a student to get a certificate.

I need an overview of all students that have an certificate.

In the example you can see that the only student that has a certificate is the one with StudentID: 111221

I am completely stuck on this. It needs to be done in one query, not multiple and without using loops.

Database diagram overview

I tried the following:

SELECT StudentID, ModuleID<br>
FROM StudentsModule<br>
WHERE ModuleID ALL (
    SELECT OnderdeelID
    FROM CourseModules 
    GROUP BY groupID
)

But i am stuck.

Upvotes: 0

Views: 401

Answers (1)

Tree Frog
Tree Frog

Reputation: 666

Try this

select s.studentId, s.moduleId, cc.courseCount, cd.courseDone
from StudentsModule s
join CourseModulec on s.moduleId = c.moduleId
join (select count(*) as courseCount, groupId from CourseModule group by groupId) as cc on cc.groupId = c.groupId
join (select count(*) as courseDone, studentId from StudentsModule s1 join CourseModule c1 on s1.moduleId = c1.moduleId group by s1.studentId) as cd on cd.studentId = s.studentId
where cc.courseCount = cd.courseDone

Upvotes: 1

Related Questions