如何将表中的一个值从另一个值分配给多个

Question

I have 3 different tables:

process table:
| projectNo | process | studio |
+-----------+---------+--------+
| 170001    | ANM BLD | APEX   |
| 170001    | ANM BLD | CANVAS |
| 170002    | CGI MOD | APEX   |
| 170003    | CGI MOD | ORBIT  |

proc_leader table:
| projectNo | process | proc_leader|
+-----------+---------+------------+
| 170001    | ANM BLD | John       |
| 170001    | ANM BLD | James      |
| 170002    | CGI MOD | Peter      |
| 170003    | CGI MOD | Kate       |

proc_checker table:
| projectNo | process | proc_checker|
+-----------+---------+------------+
| 170001    | ANM BLD | Aaron      |
| 170001    | ANM BLD | John       |
| 170002    | CGI MOD | Peter      |
| 170003    | CGI MOD | Adel       |

So what i want to do is to create html table to look like this:

| username | studio APEX | studio CANVAS | studio ORBIT |
+----------+-------------+---------------+--------------+
| Aaron    |       x     |       x       |              |
| Adel     |             |               |       x      |
| John     |       x     |       x       |              |
| James    |       x     |       x       |              |
| Kate     |             |               |       x      |
| Peter    |       x     |               |              |

So I want to assign studio from process table to the username. Is it possible to do?

Answer 1

You can UNION the leader and checker table and LEFT JOIN it with the process table.

Try this:

select
  p2.proc_leader,
  case count(case when p1.studio = 'APEX' then 1 end) > 0 then 'x' end,
  case count(case when p1.studio = 'CANVAS' then 1 end) > 0 then 'x' end,
  case count(case when p1.studio = 'ORBIT' then 1 end) > 0 then 'x' end
from process p1
left join (
  select *
  from proc_leader
  union all
  select *
  from proc_checker
) p2 on p1.projectNo = p2.projectNo
and p1.process = p2.process
group by p2.proc_leader;