mysql 连接多个表_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'

我应该将多个select查询嵌入到一个查询中吗？如有任何帮助，我们将不胜感激。

最佳答案

我猜这个问题是你只是通过orgstructure加入coursescompleted到person的，我认为你还需要参加培训：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'

见SQL Fiddle with Demo

结果是：

| 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 |