mysql - 使用 INNER JOIN 的 SQL 查询

Question

Write a query that displays the student firstname, lastname, course number, and course name for all students taking classes – use an INNER Join. Label the output columns Student First, Student Last, Course Number, and Course Name. You should have 7 rows.

是我实验室的问题。

一共有三个表students,courses,registration

我可以获得在类(class)中注册的学生的姓名

select firstname,lastname from students
inner join on registration students.studentid=registration.studentid

但是当我尝试从类(class)表中获取老师想要返回的其他数据时，它不起作用我尝试了一百万种方法，但对我来说有意义的是

select firstname,lastname,coursenumber,coursename from students,courses
inner join registration on students.studentid=registration.studentid

但它给了我一个错误 unknown column students.studentid in on 子句。

Answer 1

您非常接近，缺少registration 和courses 之间的加入条件。您将隐式和显式 INNER JOIN 奇怪地混合在一起。您加入 courses 应该是另一个 INNER JOIN，它通过 registration 加入 students。

SELECT
  firstname, /* <-- don't forget to label your columns as required */
  lastname,
  coursenumber,
  coursename 
FROM
  students
  /* `students` map into courses via many-to-many relation in `registration` */
  INNER JOIN registration on students.studentid = registration.studentid
  /* Inner join through `registration` into `courses` */
  INNER JOIN courses ON registration.courseid = courses.courseid

并且不要忘记您的列别名以满足列输出命名要求。在 SELECT 列表中使用 AS 关键字。我会把这部分作业留给你来解决。

Label the output columns Student First, Student Last, Course Number, and Course Name