MySQL 根据条件连接表,mysql查询根据多个条件连接4个表

我在mysql中有四个表,如下所示：

我想要做的是将表格连在一起,以显示是否已经为部门中的每个用户进行了培训,如果已经完成,则显示培训日期,否则说需要培训.

所以部门财务的期望输出将是这样的：

我尝试使用下面的代码,但连接变得不准确和无效.

select o.person, o.job, j.risk, r. training,c.course,

c.person,c.datecompleted

from orgstructure o

left outer join jobsrisks j

on o.job=j.job

left outer join risktraining r

on j.risk=r.risk

left outer join coursescompleted c

on o.person=c.person

where o.department='finance'

我应该将多个选择查询嵌入到一个中吗？任何帮助表示赞赏.

解决方法:

我猜这个问题是你将orgstructure加入到仅由人完成的课程中,我认为你还需要参加培训：

select o.person,

o.job,

j.risk,

r.training,

c.course,

c.person,

c.datecompleted

from orgstructure o

left outer join jobsrisks j

on o.job=j.job

left outer join risktraining r

on j.risk=r.risk

left outer join coursescompleted c

on o.person=c.person

and r.training = c.course --- add this

where o.department='finance'

如果该人已完成与该工作相关的每个风险的课程,您需要加入此人.

将整个查询放在一起您将拥有：

select o.person,

o.job,

j.risk,

r.training,

case when c.course is null then 'no' else 'yes' end TrainingCompleted,

coalesce(c.datecompleted, 'n/a') datecompleted

from orgstructure o

left outer join jobsrisks j

on o.job=j.job

left outer join risktraining r

on j.risk=r.risk

left outer join coursescompleted c

on o.person=c.person

and r.training = c.course

where o.department='finance'

结果是：

| PERSON | JOB | RISK | TRAINING | TRAININGCOMPLETED | DATECOMPLETED |

-------------------------------------------------------------------------------------------------------------------------------

| taylor chetty | manager | safety | induction course | no | n/a |

| taylor chetty | manager | security | security course | no | n/a |

| bill thompson | data clerk | bad posture | personal wellbeing course | no | n/a |

| bill thompson | data clerk | repetitive strain injury | nursing course | yes | 2000-04-13 00:00:00 |

| bill thompson | data clerk | safety | induction course | yes | 2007-12-04 00:00:00 |

| ann brown | data clerk | bad posture | personal wellbeing course | no | n/a |

| ann brown | data clerk | repetitive strain injury | nursing course | no | n/a |

| ann brown | data clerk | safety | induction course | no | n/a |

标签：mysql,join

来源： https://codeday.me/bug/20190714/1461055.html