how to know which student took ALL modules? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to know which student took ALL modules?

Author

Topic

yoyolame
Starting Member

8 Posts

Posted - 2009-01-14 : 04:05:26

Hi all, I am new here. This is my first post. Pls guide me along~
thx!

Students (StudentId, StudentName)
PK: StudentId

Modules (ModulesId, ModuleName, ModuleDesc)
PK: ModuleId

StudentsModules (StudModId, StudentId, ModuleId)
PK: StudModId
FK: StudentId REF Students
FK: ModuleId REF Modules

Now I need to find out which students took all modules.
How can i do it?

thx in advance~

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 04:13:58

Something like this

SELECT s.StudentID
FROM Students AS s
CROSS JOIN Modules AS m
LEFT JOIN StudentsModules AS sm ON sm.StudentID = s.StudentID AND sm.ModuleID = m.ModuleID
GROUP BY s.StudentID
HAVING COUNT(*) = COUNT(sm.StudModID)

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 04:17:49

SELECT sm.StudentID
FROM StudenModules AS sm
GROUP BY sm.StudentID
HAVING COUNT(DISTINCT sm.ModuleID) = (SELECT COUNT(*) FROM Modules)

E 12°55'05.63"
N 56°04'39.26"

yoyolame
Starting Member

8 Posts

Posted - 2009-01-14 : 05:20:56

this is a good method to use the total number of modules!
Since if there are 4 modules, the number of modules taken up by the student should also be 4.
But what happens each modules can take repeated modules? any idea?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-14 : 05:45:29

Not without sample data to illustrate your environment.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-14 : 08:34:19

quote:
Originally posted by yoyolame

this is a good method to use the total number of modules!
Since if there are 4 modules, the number of modules taken up by the student should also be 4.
But what happens each modules can take repeated modules? any idea?

do you mean as a hierarchy?

yoyolame
Starting Member

8 Posts

Posted - 2009-01-15 : 07:00:29

quote:
Originally posted by visakh16

do you mean as a hierarchy?

nope. It my misinterpretation.
Peso got it right!
Thumbs up for Peso!
Thank u so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources