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

mysql query to join 4 tables based on multiple criteria

提问by Smudger

I have four tables in mysql as below:

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

What I want to do is join the tables together to show if the training has been done for each user in a department, if it has been done show the date of training else say training needed.

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

so desired output for department finance would be something like:

因此，部门财务所需的输出将类似于：

I have tried with the below code but the joins become inaccurate and invalid.

我已尝试使用以下代码，但连接变得不准确且无效。

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'

should I be embedding multiple select queries into one? any help is appreciated.

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

回答by Taryn

I am going to guess that that issue is that you are joining the orgstructureto coursescompletedby persononly, I think you also need to join on training:

我会猜测，这个问题是你加入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'

You need to join on the person plus if that person has a completed the course for each risk associated with the job.

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

Putting the entire query together you will have:

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

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'

See SQL Fiddle with Demo

参见SQL Fiddle with Demo

The result is:

结果是：

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