The code below is completely wrong and does not work at all. Im basically trying to look through my tables and compile a list of DeptName and the total student number for a department where a department has more than 40 students.
Im confused about joins in general and if someone could explain and show where im going wrong. im sure there is also other problems so any help with them would help
So basically one department is connected to one module, and a student is enrolled in a module. A student cannot take a module outside of their department. So each student should have one module that connects to one department
All of the ID fields in other tables are foreign keys as you can guess and changing the tables is not what I want to do here I just want to do this query as this stands
Relevant tables columns
SELECT Department.DeptName, COUNT(Student.StudentID) AS 'No of Students' FROM Department LEFT JOIN Module ON Department.DeptID= Module.DeptID LEFT JOIN Enrolment ON Module.ModuleID= Enrolment.StudentID LEFT JOIN Student.StudentID GROUP BY(Department.DeptID) HAVING COUNT(Student.StudentID)>=40
I have not included every table here as there are quite a lot.
But unless i’ve got this completely wrong you don’t need to access a ModuleID in a staff table for the module they teach or something not relevant to this at all. As no student or Dept details are in there.
If that is the case i will fix it very quickly.
SELECT Department.DeptName, COUNT(Student.StudentID) AS 'No of Students' FROM Department LEFT JOIN Module ON Department.DeptID= Module.DeptID LEFT JOIN Enrolment -- problem #1: ON Module.ModuleID= Enrolment.StudentID -- problem #2: LEFT JOIN Student.StudentID -- problem #3: GROUP BY(Department.DeptID) HAVING COUNT(Student.StudentID)>=40
- You’re joining these two tables using the wrong field. Generally when the modeling is done correctly, you should use
- The right side of any
JOINoperator has to be a table, not a column.
- You have to group by every column in the select clause that is not part of an aggregate function like
COUNT. I recommend that you select the DeptID instead of the name, then use the result of this query to look up the name in a subsequent select.
Note : Following code is untested.
WITH bigDepts AS ( SELECT DeptId, COUNT(StudentID) AS StudentCount FROM Department JOIN Module USING ( DeptID ) JOIN Enrolment USING ( ModuleID ) JOIN Student USING ( StudentID ) GROUP BY DeptID HAVING COUNT(StudentID)>=40 ) SELECT DeptID, DeptName, StudentCount FROM Department JOIN bigDepts USING ( DeptID )