我有五(5)个表:学生,科目,period_one,period_two和period_three . 我正在执行左连接以从这五个表中选择值:
Students Table (students)
student_id | Name
--------------|-------
1 |John
2 |Peter
3 |Flomo
Subjects Table (subjects)
subject_id |SubjectName
-----------------|-------
math101 |Mathematics
eng201 |English
lang303 |Language Arts
Period One Table (period_one)
id|student_id |subject_id| score
--------------|----------|-----
1 |1 | math101 |99
2 |2 | eng201 |88
3 |3 | lang303 |77
Period Two Table (period_two)
id|student_id |subject_id| score
--------------|----------|-----
1 |1 | math101 |100
2 |2 | eng201 |60
3 |3 | lang303 |65
Period Three Table (period_three)
id|student_id |subject_id| score
--------------|----------|-----
1 |1 | math101 |71
2 |2 | eng201 |51
3 |3 | lang303 |71
这是我用来检索记录的查询 Query1 :
SELECT period_one.student_id, period_one.subject_id, period_one.score, period_two.score,period_three.score
from period_one
LEFT JOIN period_two
ON period_one.subject_id = period_two.subject_id
AND period_one.student_id = period_two.student_id
LEFT JOIN period_three
on period_one.subject_id = period_three.subject_id
AND period_one.student_id = period_three.student_id
WHERE period_one.student_id = 10
上面代码的问题是,如果我正在查找的学生ID不在第一个表(periodOne)中,左连接正在应用于查询,则返回null,即使该学生的记录在其他表中(periodTwo)和期间三) . 我查看了这个问题(https://www.w3schools.com/sql/sql_join_left.asp),并证实这不是最好的做事方式 .
I THEN MADE SOME CHANGES
所以,我更新了我的查询,看起来像这样( Query2 ):
SELECT students.student_id, period_one.score, period_one.subject_id,
period_two.score, period_two.subject_id, period_three.score,
period_three.subject_id
from students
LEFT JOIN period_one
ON students.student_id = period_one.student_id
LEFT JOIN period_two
ON students.student_id = period_two.student_id
LEFT JOIN period_three
ON students.student_id = period_three.student_id
在哪里students.student_id = 3 OR period_one.student_id = 3 OR period_two.student_id = 3 OR period_three.student_id = 3
这完美地工作,因为学生表是所有期间表 are referencing 的主表 . 如果学生ID不在period_one和period_two表中,而是在period_there中,则返回该表的studentId,subjectId和score .
THEN ANOTHER PROBLEM POPS OUT
在我更新代码之前,我正在按照我想要的方式显示记录,但是没有按照需要的方式获取/检索记录 . 这就是促使我改变查询的原因,因为我发现这是问题所在 .
现在,基于我的第一个查询( Query1 ),我从select语句中的各个表中选择了subject_id . 当我显示记录时,我传递了subject_id从查询返回到获取该id的主题名称的函数 . 这就是我显示结果的方式:
如果学生id在表中分配给from子句,则此方法有效,否则不返回任何内容 . 这就是我改变代码的原因 .
但现在我已将代码更改为( Query2 )我'm unable to display the subject id and its name because it is not within the students table. Here is a gist on how I' m显示我的记录:https://gist.github.com/nathansiafa/e9d22791800d4ba3a00e2b98de52baec
有没有办法让它更好地运作?将欣赏建议和反馈 . 谢谢!