带内连接的MySQL查询返回空集

This has really been bugging me for a while. I'm trying to write an SQL query to return all courses (names), their instructors (names),and their prerequisite course (if they exist). But my query just isn't working and I don't know what to do anymore. I keep getting an empty set returned.

这真的让我困扰了一段时间。我正在尝试编写一个SQL查询来返回所有课程（名称），他们的教师（姓名）和他们的必修课程（如果存在）。但我的查询不起作用，我不知道该怎么办。我一直得到一个空集。

SELECT course, instructors.inst, 
something 
FROM courses a inner join instructors ON a.course_id = instructors.inst_id
INNER JOIN courses b ON a.prereq = b.course_id where a.prereq IS NOT NULL;

Am I doing something wrong with the multiple inner joins? Any help would be greatly appreciated.

我是否对多个内连接做错了什么？任何帮助将不胜感激。

2 个解决方案

#1

LEFT OUTER JOIN should give You the result.Inner join will return results only in case you have data in all of the joined tables.

LEFT OUTER JOIN应该为您提供result.Inner join将返回结果，以防您在所有连接表中都有数据。

#2

Try this

尝试这个

SELECT a.course_name AS "Course", instructors.inst_name AS "Instructor", 
b.course_name AS "Prereq"
FROM courses a
LEFT OUTER JOIN instructors ON a.course_id = instructors.inst_id
LEFT OUTER JOIN courses b ON a.prereq = b.course_id

#1