'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
sqldatabase


Solution 1:[1]

Try this

SELECT firstname, lastname, classname, trainerName
FROM member
INNER JOIN enrollment 
ON enrolment.memberID = member.memberID
INNER JOIN _class
ON enrollment.classID = class.classID
INNER JOIN trainer
ON trainer.trainerID = member.trainerID

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions