MySQL 连接返回的行数多于预期的行数

Question

我想要来自三个以上带有连接的 MySQL 表的一些记录，但我得到了意外的记录，这个查询出了什么问题..

1)

承认卡表

class_id|exam_id|subject_id|  exam_date  |exam_time_from|exam_time_to
   1       1         S1      02/12/2016    11:00            02:00    
   2       1         S1      01/12/2016    11:00            02:00    
   2       1         S4      02/12/2016    11:00            02:00    

主题表

section_id_fk|subject_code|subject_name
   1             S1          HINDI
   2             S1          HINDI
   2             S4          COMPUTER

class_details 表

class_id_pk|class_name
   1           FIRST
   2           SECOND

section_details表

section_id_pk|class_id_fk|section_name|
   1               1          A
   2               2          A

现在我想从包含三个记录的admit_card表中获取记录，但这里的连接是强制性的，因为在准考证中，我有class_id exam_id和subject_id列，它们可以通过他们的上级表获取。现在我的查询是这样的:

SELECT class_name,section_name,subject_code,subject_name,ac.exam_date, ac.exam_time_from,ac.exam_time_to 
FROM admit_card ac 
LEFT OUTER JOIN section_details sd ON sd.section_id_pk = ac.class_id 
JOIN class_details cd ON cd.class_id_pk = sd.class_id_fk 
JOIN subjects ON sd.section_id_pk = subjects.section_id_fk 
JOIN admit_card ON admit_card.subject_id = subjects.subject_code;

预期答案

1) FIRST  |A|    S1|  HINDI   |02/12/2016 |  11:00| 02:00
2) SECOND |A|    S1|  HINDI   |01/12/2016 |  11:00| 02:00
3) SECOND |A|    S4|  COMP    |02/12/2016 |  11:00| 12:00

Answer 1

您不应在查询中出现两次 admit_card。要将不同表的列连接起来，请在同一个 ON 子句中指定它们。

SELECT class_name,section_name,subject_code,subject_name,ac.exam_date, ac.exam_time_from,ac.exam_time_to 
FROM admit_card ac 
LEFT OUTER JOIN section_details sd ON sd.section_id_pk = ac.class_id 
JOIN class_details cd ON cd.class_id_pk = sd.class_id_fk 
JOIN subjects ON sd.section_id_pk = subjects.section_id_fk AND ac.subject_id = subjects.subject_code

结果:

+------------+--------------+--------------+--------------+---------------------+----------------+--------------+
| class_name | section_name | subject_code | subject_name | exam_date           | exam_time_from | exam_time_to |
+------------+--------------+--------------+--------------+---------------------+----------------+--------------+
| FIRST      | A            | S1           | HINDI        | 2016-02-12 00:00:00 | 11:00          | 02:00        |
| SECOND     | A            | S1           | HINDI        | 2016-01-12 00:00:00 | 11:00          | 02:00        |
| SECOND     | A            | S4           | COMPUTER     | 2016-02-12 00:00:00 | 11:00          | 02:00        |
+------------+--------------+--------------+--------------+---------------------+----------------+--------------+