SQL – listing members on enrolled classes

I am trying to figure out why this isn’t working. I am trying to select each member’s names and the classes that each member is enrolled on together with the class trainers name.

This is what I have at the minute:

SELECT 
    firstName, lastName, className, trainerName 
FROM 
    member, _class, trainer, enrolment 
WHERE 
    enrolment.memberID = member.memberID 
    AND enrolment.classID = _class.classID;

Any help or tips would be appreciated as I am still learning SQL.

Extra info:

• enrolment table has columns entrolmentID, memberID and classID
• trainer table has columns trainerID and trainerName
• member table has memberID, firstName, lastName, age, trainerID
• class table has classID, className, _day and trainerID

Answer

The valid syntax for a JOIN statement is:

SELECT FIELD1
    ,...FIELDN
FROM TABLE1 T1
INNER JOIN TABLE2 T2 ON T1.FIELDX = T2.FIELDY

So in your case, use:

SELECT firstName
    ,lastName
    ,className
    ,trainerName
FROM member
INNER JOIN enrolment ON enrolment.memberID = member.memberID
INNER JOIN _class ON enrolment.classID = _class.classID
INNER JOIN trainer ON trainer.trainerID = member.trainerID