MySQL - 从多个表中选择多列

Question

我有三张 table 。其中两个是独立的不相关表(学生和科目)，第三个(条目)是用外键(student_id 和 subject_id)将它们链接起来的表。

以下是所有包含记录的表:

学生:

+------------+------------+-----------+---------------------+----------------------+
| student_id | first_name | surname   | email               | reg_date             |
+------------+------------+-----------+---------------------+----------------------+
|          1 | Emma       | Harvey    | emmah@gmail.com     | 2012-10-14  11:14:13|
|          2 | Daniel     | ALexander | daniela@hotmail.com | 2014-08-19  08:08:23 |
|          3 | Sarah      | Bell      | sbell@gmail.com     | 1998-07-04  13:16:32 |
+------------+------------+-----------+---------------------+----------------------+

主题:

+------------+--------------+------------+----------------+
| subject_id | subject_name | exam_board | level_of_entry |
+------------+--------------+------------+----------------+
|          1 | Art          | CCEA       | AS             |
|          2 | Biology      | CCEA       | A              |
|          3 | Computing    | OCR        | GCSE           |
|          4 | French       | CCEA       | GCSE           |
|          5 | Maths        | OCR        | AS             |
|          6 | Chemistry    | CCEA       | GCSE           |
|          7 | Physics      | OCR        | AS             |
|          8 | RS           | CCEA       | GCSE           |
+------------+--------------+------------+----------------+    

条目:

+----------+---------------+---------------+------------+
| entry_id | student_id_fk | subject_id_fk | entry_date |
+----------+---------------+---------------+------------+
|        1 |             1 |             1 | 2012-10-15 |
|        2 |             1 |             4 | 2011-09-21 |
|        3 |             1 |             3 | 2015-08-10 |
|        4 |             2 |             6 | 1992-07-13 |
|        5 |             3 |             7 | 2013-02-12 |
|        6 |             3 |             8 | 2016-01-14 |
+----------+---------------+---------------+------------+

我将如何选择每个学生的电子邮件(学生表)和科目名称(科目表)，以及考试委员会 OCR 的学生(使用条目表)？因此，基本上使用一种开销表从多个表中选择多个列。所需的输出是这样的:

email           | subject_name
------------------------------
emmah@gmail.com | Computing
sbell@gmail.com | Physics

(抱歉，我不知道如何在问题框中正确重新创建表格)。

我认为它始于

SELECT student.email, subjects.subject_name
FROM students, subjects

但在那之后我就没什么想法了，除了它使用连接之外。

非常感谢您的帮助!所以谢谢你!

Answer 1

您走在正确的道路上。您需要做的是使用 entries 表通过主键和外键连接其他两个表，如下所示:

SELECT student.email, subjects.subject_name
FROM students
INNER JOIN entries  ON students.student_id = entries.student_id_fk 
INNER JOIN subjects ON subjects.subject_id = entries.subject_id_fk 
WHERE subjects.exam_board = 'OCR'