在select语句中进行子选择，返回null

I'm trying to list the name of courses and their prereqs (if there are any) in a university database. The schema for these tables look like this

我正在尝试列出大学数据库中的课程名称及其先决条件(如果有的话)。这些表的架构如下所示

course(course_id, title, dept_name, credits)
prereq(course_id, prereq_id)

This is my query

这是我的疑问

SELECT title, (SELECT title
              FROM course NATURAL JOIN prereq c2
              WHERE c1.prereq_id = c2.course_id)
FROM course NATURAL LEFT JOIN prereq c1;

My results are returning null for all values of the second select statement. I have a feeling it has something to do with the value of c1 not being known at the time of the second select statement. Is there a way around this or a better query that can be used?

我的结果为第二个select语句的所有值返回null。我觉得它与第二个select语句时未知的c1的值有关。有没有办法绕过这个或更好的查询可以使用?

2 个解决方案

#1

I'm trying to list the name of courses and their prereqs (if there are any) in a university database.

我正在尝试列出大学数据库中的课程名称及其先决条件(如果有的话)。

You can do this instead:

你可以这样做:

SELECT 
  c1.title "Course Title",
  IFNULL(c2.title, "Has No Preqs") "Preq Course Title"
FROM course c1
LEFT JOIN prereq p ON p.course_id = c1.course_id
LEFT JOIN course c2 ON p.prereq_id= c2.course_id

SQL Fiddle Demo

But this won't work in case there are many prerequisite courses for any course.

但是,如果任何课程都有许多必修课程,这将不起作用。

In this case you have to use GROUP_CONCAT as @danihp pointed out in his answer like so:

在这种情况下,您必须使用GROUP_CONCAT作为@danihp在他的答案中指出,如下所示:

SELECT 
  c1.title "Course Title",
  GROUP_CONCAT(IFNULL(c2.title, "Has No Preqs")) "Preq Course Title"
FROM course c1
LEFT JOIN prereq p ON p.course_id = c1.course_id
LEFT JOIN course c2 ON p.prereq_id= c2.course_id
GROUP BY c1.title ;

SQL Fiddle Demo

#2

You are looking for group_concat aggregation function:

您正在寻找group_concat聚合函数:

SELECT 
   title,  
   group_concat( distinct c2.title ) as prereqs
FROM course 
NATURAL LEFT JOIN prereq c1
left outer join course c2 on c1.course_id = c2.course_id
group by course.course_id;