我在 PostgreSQL 数据库中有四个表(a、b、c、d):
在此处输入图片说明
我可以使用 subject.id 和 project.id 唯一键连接这些表。我的查询是:
SELECT subject_id, firstname, lastname, national_id, project_title, project_id, note_template FROM project_subject JOIN subject ON subject.id = project_subject.subject_id JOIN project ON project.id = project_subject.project_id JOIN subject_note ON subject_note.subject_id = project_subject.subject_id WHERE project_id = xxxx
我的问题是在表 d (subject_notes) 中,一个主题可以有多个注释 (varchar) 加上可选的 note_template (int)。我想加入包含subject_id、firstname、lastname、national_id、project_title、project_id和note_template列的表。当我加入四个表时,我得到几个相同主题行和不同注释的结果。我想要一个表格,其中每个主题仅列出一次,并且仅在 note_template=16 时才将值添加到最后一列。其余主题的 note_template 将具有空值。
d (subject_notes)
(varchar)
note_template (int)
subject_id、firstname、lastname、national_id、project_title、project_id
note_template
note_template=16
将最后一个LEFT连接更改为一个连接并note_template = 16在ON子句中添加条件:
note_template = 16
LEFT JOIN subject_note ON subject_note.subject_id = project_subject.subject_id AND subject_note.note_template = 16