mysql - 如何为3个表编写子查询

Question

我需要使用子查询打印第一个表中选择物理和化学的学生姓名、第二个表中的类(class)编号以及第三个表中的类(class)标题

mysql> select * from tudents;                                                                     

| student_no | student_name | age  |
+------------+--------------+------+
|          1 | Michael      |   19 |
|          2 | Doug         |   18 |
|          3 | Samantha     |   21 |
|          4 | Pete         |   20 |
|          5 | Ralph        |   19 |
|          6 | Arnold       |   22 |
|          7 | Michael      |   19 |
|          8 | Jack         |   19 |
|          9 | Rand         |   17 |
|         10 | Sylvia       |   20 |

mysql> select * from student_enrollment;

+------------+-----------+
| student_no | course_no |
+------------+-----------+
|          1 | CS110     |
|          1 | CS180     |
|          1 | CS210     |
|          2 | CS107     |
|          2 | CS220     |
|          3 | CS110     |
|          3 | CS180     |
|          4 | CS220     |
|          5 | CS110     |
|          5 | CS180     |
|          5 | CS210     |
|          5 | CS220     |
|          6 | CS110     |
|          7 | CS110     |
|          7 | CS210     |

mysql> select * from courses;

+-----------+---------------------+---------+
| course_no | course_title        | credits |
+-----------+---------------------+---------+
| CS110     | Pre Calculus        |       4 |
| CS180     | Physics             |       4 |
| CS107     | Intro to Psychology |       3 |
| CS210     | Art History         |       3 |
| CS220     | US History          |       3 |

我可以使用下面的查询从first_table中提取名称

select a.student_name 
from students a 
where a.student_no in (select student_no 
                       from student_enrollment  
                       where course_no in (select course_no 
                                           from courses 
                                           where course_title in ('Physics', 'US History')));

下面的查询有 210 行。错误是什么？

select 
    a.student_name, b.course_no, c.course_title 
from 
    students a, student_enrollment b, courses c 
where 
    a.student_no in (select student_no 
                     from student_enrollment 
                     where b.course_no in (select course_no 
                                           from courses 
                                           where course_title in ('Physics', 'US History')));

Answer 1

select
    students.student_name,student_enrollment.course_no,courses.course_title
from students 
join student_enrollment
    on students.student_no=student_enrollment.student_no
join courses
    on courses.course_no=student_enrollment.course_no
where courses.course_title in ('Physics', 'US History');