Trying to query 3 tables using INNER JOIN

[Presto-X]

Hello everyone,

 

I'm doing a simple query of 3 tables the first is the main table to get a list of upcoming classes, and the other two is to get the course title and the instructor's name, the problem is if an instructor has not been assgned the result does not get returned it just skips that row, I'm guessing it's becuase the instructor's id is 0 not sure?  If the courses.id and users.id is set it will get the row, but if ether of them are set to 0 then it skips the row.  Can you take a look at my query and see where I'm going wrong, thanks everyone.

 

SELECT
classes.id,
classes.state,
classes.date AS created,
classes.city,
users.`name` AS instructor,
courses.title AS course
FROM
classes
INNER JOIN users ON users.id = classes.instructor_id
INNER JOIN courses ON courses.id = classes.courseid

 

[kickstart]

Hi

 

If you want a row returned when one of the joined tables doesn't have a matching row then use an outer join

 

SELECT
classes.id,
classes.state,
classes.date AS created,
classes.city,
users.`name` AS instructor,
courses.title AS course
FROM
classes
INNER JOIN users ON users.id = classes.instructor_id
LEFT OUTER JOIN courses ON courses.id = classes.courseid

 

All the best

 

Keith

[Presto-X]

Thanks so much Keith, I really need to learn my joins better, thanks again for the help