Full outer join on multiple tables with field concatenation in MySQL

Question

I have four tables: acting, writing, film, tech

email         | skill
-----------------------
foo@bar.com   | acting
foo1@bar.com  | acting
foo2@bar.com  | acting
...           | ...

email         | skill
-----------------------
foo@bar.com   | writing
foo7@bar.com  | writing
foo8@bar.com  | writing
...           | ...

email         | skill
-----------------------
foo4@bar.com  | film
foo9@bar.com  | film
foo12@bar.com | film
...           | ...

email         | skill
-----------------------
foo3@bar.com  | tech
foo4@bar.com  | tech
foo7@bar.com  | tech
...           | ...

I want to create a new table skills, such that every email address is included, and the skills pertaining to each address are concatenated into the skill field.

email         | skill
-----------------------
foo@bar.com   | acting, writing
foo1@bar.com  | writing, film
foo2@bar.com  | acting, film, tech
...           | ...

I've mucked around with full outer joins via union but I can't seem to get it right.

UPDATE: I realize how poorly these tables are designed. I am migrating data from a frankenstein legacy CRM with the above tables, and I need the values comma separated in the new table so I can import them into a new CRM.

Why would you want to create such a table, rather than fetch such a resultset with a query as & when required? — eggyal, Oct 18 '12 at 14:03
See discussion of mysql `GROUP_CONCAT` here: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field — Palpatim, Oct 18 '12 at 14:03
Should the field contain comma-separated text? It is better to store skill `id` in rows for each e-mail. — Devart, Oct 18 '12 at 14:08
Moreover, why are you storing this data across four tables instead of in a single table (with differing values in the `skill` column)? — eggyal, Oct 18 '12 at 14:11

score 3 · Accepted Answer · answered Oct 18 '12 at 14:02

3

SELECT email, GROUP_CONCAT(skill ORDER BY skill) skill
FROM
(
    SELECT email, skill FROM acting
    UNION
    SELECT email, skill FROM writing
    UNION
    SELECT email, skill FROM film
    UNION
    SELECT email, skill FROM tech
) x
GROUP BY email

SQLFiddle Demo

answered Oct 18 '12 at 14:02

John Woo

258,903
69
498
492

Can I ask what the `x` does here? – felix Oct 23 '12 at 19:02
x is called the alias for the subquery. If you try to remove that, it will generate an error. – John Woo Oct 23 '12 at 21:28

Full outer join on multiple tables with field concatenation in MySQL

1 Answers1

SQLFiddle Demo