phpmyadmin Question posted in
The official documentation can be found here.

Having trouble a query and specifically with joins – Phpmyadmin

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

Table Department DeptID, DeptName, Faculty, Address

Table Modules ModuleID, ModuleName, DeptID, Programme

Table Students StudentID,StudentName,DoB,Address,StudyType,`

Table Enrolments EID,StudentID,ModuleID,Semester,Year

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.

Tags:

3

Answers


  1. 0

    Instead of left join you need to use inner join since you need to select related rows only from those three tables.

    Groupy by and having clause seems fine. Since you need departments with more than 40 students instead of >= please use COUNT(e.StudentID)>40

    SELECT d.DeptName, COUNT(e.StudentID) AS 'No of Students'  FROM Department d INNER JOIN Module m ON d.DeptID= m.DeptID inner JOIN Enrolment e ON m.ModuleID=  e.StudentID LEFT JOIN Student.StudentID 
GROUP BY(d.DeptName)
HAVING COUNT(e.StudentID)>40
    Login or Signup to reply.
  2. 0

    So your join clause was a bit iffy to students as you wrote it, and presumably these should all be inner joins.

    I’ve reformatted your query using aliases to make it easier to read.

    Since you’re counting the number of rows per DeptName you can simply do count(*), likewise in your having you are after counts greater than 40 only. Without seeing your schemas and data it’s not possible to know if you might have duplicate Students, if that’s the case and you want distinct students count can amend to count(distinct s.studentId)

    select d.DeptName, Count(*) as 'No of Students'  
from Department d
join Module m on m.DeptId=d.DeptId
join Enrolment e on e.StudentId=m.ModuleId
join Students s on s.StudentId=e.studentId
group by(d.DeptName)
having Count(*)>40

    Also, looking at your join conditions, is the Enrolement table relevant?

    select d.DeptName, Count(*) as 'No of Students'  
from Department d
join Module m on m.DeptId=d.DeptId
join Students s on s.StudentId=m.moduleId
group by(d.DeptName)
having Count(*)>40
    Login or Signup to reply.
  3. 0 
    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
    1. You’re joining these two tables using the wrong field. Generally when the modeling is done correctly, you should use USING instead of ON for joins
    2. The right side of any JOIN operator has to be a table, not a column.
    3. 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 )
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search