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 to share