SQL Query - Count () and Inner Join

I want to use an inner join to display the student ID, name, and the total number of scheduled hours a student has per week when the student has more than 4 hours per week.

I have three tables required here: student, studentReg and roomBooking as follows

 student

 id | fname | surname | courseCode

studentReg

sID | modCode

roomBooking

bookingID | roomCode | moduleCode | dayReq | timeReq | semester | classSize

      

The SQL query I have so far is

 SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM (student INNER JOIN studentReg ON student.id = studentReg.sID
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode)
 HAVING COUNT (moduleCode) > 4;

      

and when i try to run this i get "syntax error in expression"

Can anyone help me to solve the problem?

+3


source to share


1 answer


Not sure if with nested connection in ms access, but I would try something like this

SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM student 
 INNER JOIN (studentReg 
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode)
        ON student.id = studentReg.sID
 GROUP BY id, fname, surname
 HAVING COUNT (moduleCode) > 4

      



or maybe

SELECT COUNT(moduleCode) AS [Lecture Hours],
 id, fname, surname
 FROM (student INNER JOIN studentReg ON student.id = studentReg.sID)
        INNER JOIN roomBooking ON studentReg.modCode = roomBooking.moduleCode
 GROUP BY id, fname, surname
 HAVING COUNT (moduleCode) > 4;

      

+2


source







All Articles