Help in PHP Query (Foreign Keys)

[NoviceCoder37]

Hello,

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?

Thank you.

[diafol]

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 don't understand why you've got a trainerName (FK) in the course table. It looks as though that you're relating tables via your link table (trainerCourses).

Including trainerName in course doesn't seem a valid inclusion to me.

[NoviceCoder37]

Oops, that was supposed to be trainerID as a FK in course.

This is my code so far but now it returns an empty result set. :S

SELECT COUNT(course.courseID)
FROM course, trainer
WHERE course.courseID = trainer.trainerID;

SELECT COUNT(trainerCourses.attendanceStatus) 
FROM course, trainerCourses
WHERE trainerCourses.courseID = course.courseID AND trainerCourses.attendanceStatus = '4';

SELECT trainer.trainerName
FROM course, trainer
WHERE course.courseID = trainer.trainerID;

[diafol]

Oops, that was supposed to be trainerID as a FK in course.

Even so, I can't see why it should be there. You're effectively limiting a course to a particular trainer type. This then obviates the need for a link table.

[NoviceCoder37]

Ok. I see your point but it is mentioned in the question even though it's not efficient. So, let's say if I delete that field from course, how would I go about displaying the required information?

ERROR : "#1242 - Subquery returns more than 1 row"

SELECT trainer.trainerName, COUNT( trainerCourses.courseID ) , COUNT( trainerCourses.attendanceStatus ) 
FROM  `trainerCourses` , trainer
WHERE (
trainerCourses.trainerID = trainer.trainerID
)
AND (
trainerCourses.attendanceStatus =  '4'
)
AND (

SELECT trainer.trainerName
FROM trainer, trainerCourses
WHERE trainer.trainerID = trainerCourses.trainerID
);

This is what I'm working on now. I know the answer should be much simpler than that but I cant seem to figure it out.

[diafol]

OK, back to the question. :)

I'm not sure I follow the logic of the attendstatus = 4
For me, the structure should be:

trainer table
trainer_id
trainer_name

courses table
course_id
course_name

trainer_course table
trainer_id
course_id

If the trainer did not attend the course, he/she would not have a record in the trainer_course table. SO, you just need a straight count of the link table:

SELECT t.trainer_name, COUNT(tc.course_id) AS num_courses FROM trainer_course AS tc INNER JOIN trainer AS t ON tc.trainer_id = t.trainer_id GROUP BY tc.trainer_id