I have created 3 tables: trainer, course, trainerCourses.
Table trainer has the following fields: trainerID(PK) , trainerName, email and a brief summary.
Table course has the following fields: courseID(PK), courseDate and trainerName(FK)
Table trainerCourses has the following fields: courseID(FK), trainerID(FK) and attendanceStatus (int where 0 is absent and 4 is present)
I want to write a PHP page to display trainerName where next each name is the number of all the courses they teach and the number of attendances they have.
I am a little confused as to how to do this because of the foreign keys. I have written this query but it only returns the first trainerName and the count of ALL the courses and attendances.
SELECT trainer.trainerName, COUNT( trainerCourses.attendanceStatus ) , COUNT( course.courseID ) FROM trainer, course, trainerCourses WHERE trainer.trainerID = trainerCourses.trainerID AND trainer.trainerID = course.trainerID AND course.courseID = trainerCourses.courseID LIMIT 0 , 30
Can anyone review my query and tell me what I'm doing wrong?