How to assign one value from table to multiple from another

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?

It's just a join. The formatting would normally be done in PHP — Strawberry, Jan 18 '17 at 07:50

score 1 · Accepted Answer · answered Jan 18 '17 at 04:31

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;

answered Jan 18 '17 at 04:31

Gurwinder Singh

38,557
6
51
76

What if there will be more studios? not only three. – Yevgeniy Bagackiy Jan 18 '17 at 04:41
That require dynamic pivoting and AFAIK, MySQL doesn't have any built-in support for that. Take a look at this procedural method using dynamic SQL - http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns – Gurwinder Singh Jan 18 '17 at 04:49

score 1 · Answer 2 · answered Jan 18 '17 at 04:33

1

I think the following does what you want:

select proc_leader,
       max(case when studio = 'APEX' then 'x' else '' end) as APEX,
       max(case when studio = 'CANVAS' then 'x' else '' end) as CANVAS,
       max(case when studio = 'ORBIT' then 'x' else '' end) as ORBIT
from ((select l.proc_leader, p.studio
       from proc_leader l join
            process p
            on l.projectNo = p.projectNo
      ) union all
      (select c.proc_leader, p.studio
       from proc_checker c
            process p
            on c.projectNo = p.project_no
      )
     ) pp
group by proc_leader;

answered Jan 18 '17 at 04:33

Gordon Linoff

1,242,037
58
646
786

What if there will be more studios? I will have to change code regularly, how to avoid it? – Yevgeniy Bagackiy Jan 18 '17 at 04:45
@YevgeniyBagackiy . . . If you need to dynamically match all studios, then you need dynamic SQL. You can start by googling "mysql dynamic pivot". – Gordon Linoff Jan 19 '17 at 01:43

score 1 · Answer 3 · answered Jan 18 '17 at 04:50

1

SELECT
  u.username,
  max(if (p.studio = 'APEX',   'x', '')) AS APEX,
  max(if (p.studio = 'CANVAS', 'x', '')) AS CANVAS,
  max(if (p.studio = 'ORBIT',  'x', '')) AS ORBIT
FROM (
  SELECT proc_leader  AS username,projectNo FROM proc_leader
  UNION
  SELECT proc_checker AS username,projectNo FROM proc_checker
) AS u
LEFT JOIN process AS p ON p.projectNo = u.projectNo
GROUP BY 1

answered Jan 18 '17 at 04:50

SIDU

2,258
1
12
23

Yeah but the problem is there can be more than 3 studios, there are 13 studios in total that can be used by user, even if i hardcode each one in query, there still option for user to create new studio. and after user will do it I will have to change query again. Is there a way to avoid it? – Yevgeniy Bagackiy Jan 18 '17 at 04:57

How to assign one value from table to multiple from another

3 Answers3