mysql - 多个 SQL JOIN 语句

Question

我必须从两个表进行查询，例如，这是我的“学生”表的表结构:

-------------------------------------------
id  |  Name  |  mom_job_id  |  dad_job_id
-------------------------------------------
1   | Test1  | 1            |  2
2   | Test2  | 3            |  1
-------------------------------------------

我有这样的“工作”表:

---------------------------
id  | job_name
---------------------------
1   | designer
2   | writer
3   | programmer
---------------------------

我想在 student 表上选择一条记录，并用“job table”上的 job_name 替换“mom_job_id”和“dad_job_id”

我试过这个查询:

SELECT student.id, job.job_name as mom_job, job.job_name as dad_job
FROM student
INNER JOIN job ON mom_job_id = job.id
WHERE id=1

该查询工作正常，但爸爸的工作设置为与妈妈的工作相同，所以我添加了另一个 INNER JOIN 并像这样进行查询:

SELECT student.id, job.job_name as mom_job, job.job_name as dad_job
FROM student
INNER JOIN job ON mom_job_id = job.id
INNER JOIN job ON dad_job_id = job.id
WHERE id=1

查询有效但未返回任何记录。那么我该如何解决呢？我相信问题出在 job.id

Answer 1

你已经接近这个了，每次加入作业表时只需给它一个不同的别名即可。

SELECT s.id, job_mom.job_name as mom_job, job_dad.job_name as dad_job
FROM student s
INNER JOIN job job_mom ON s.mom_job_id = job_mom.id
INNER JOIN job job_dad ON s.dad_job_id = job_dad.id
WHERE s.id=1

如果 job 表 JOIN 没有返回一行，而你仍然想显示学生表数据，你可以使用 LEFT JOIN:

SELECT s.id, job_mom.job_name as mom_job, job_dad.job_name as dad_job
FROM student s
LEFT JOIN job job_mom ON s.mom_job_id = job_mom.id
LEFT JOIN job job_dad ON s.dad_job_id = job_dad.id
WHERE s.id=1