Populate many-to-many person-color table using three tables in MySQL

Question

I'm trying to normalize a table in MySQL, converting a table with many similar columns to a many-to-many relationship with two columns. I have following tables:

person:

+----+------+
| id | name |
+----+------+
| 1  | John |
| 2  | Anna |
| 3  | Leon |
+----+------+

person_temp:

+------+--------+--------+--------+--------+
| name | color1 | color2 | color3 | color4 |
+------+--------+--------+--------+--------+
| John |  red   |  blue  |  green |        |
| Anna |  green | yellow |        |        |
| Leon |  blue  |  red   |        |        |
+------+--------+--------+--------+--------+

color:

+----+--------+
| id |  name  |
+----+--------+
| 1  | red    |
| 2  | blue   |
| 3  | green  |
| 4  | yellow |
+----+--------+

I would like to delete person_temp after populating the many-to-many relation table like this:

person-color:

+-----------+----------+
| person_id | color_id |
+-----------+----------+
|     1     |    1     |
|     1     |    2     |
|     1     |    3     |
|     2     |    3     |
|     2     |    4     |
|     3     |    2     |
|     3     |    1     |
+-----------+----------+

However, I haven't found any solution to my query. The only relationship I have to the id of the person is the name in person_temp. I know names are unique in person, so it wouldn’t be a problem to use them for the query.

I tried with this SQL, but it isn’t working because person_temp doesn't have an id column.

INSERT INTO `person_color`
SELECT p.id, c.id
FROM (
  SELECT id, color1 color
  FROM person_temp
  UNION
    SELECT id, color2 FROM person_temp
  UNION
    SELECT id, color3 FROM person_temp
  UNION
    SELECT id, color4 FROM person_temp
  UNION
    SELECT id, color5 FROM person_temp
) p
  JOIN color c
    ON c.name = p.color;

If you do not have `id` in `person_temp`, then you have to join to `person` table with `name` on both the tables. — Viki888, Nov 22 '16 at 13:35
You are not normalizing, either in the sense of coming up with a relational design or in the sense of higher normal forms. You're just improving the design. PS Your code was very close, see my answer. — philipxy, Nov 23 '16 at 02:13

philipxy · Answer 1 · 2016-11-23T02:13:53.573

Your original query is straightforward and close. Besides selecting names not ids from person_temp, you just need to JOIN those names with their ids from person at the end. You don't need to do all those JOINs to person_temp individually.

INSERT INTO person_color
SELECT p.id AS person_id, c.id AS color_id
FROM (
     SELECT name, color1 AS color FROM person_temp
  UNION
    SELECT name, color2 FROM person_temp
  UNION
    SELECT name, color3 FROM person_temp
  UNION
    SELECT name, color4 FROM person_temp
  UNION
    SELECT name, color5 FROM person_temp
) nc
JOIN color c
    ON c.name = nc.color
JOIN person p
    ON p.name = nc.name;

Table rows do not have an order, so it's pointless to ORDER BY p.id, c.id in an INSERT. Output rows have an order.

PS You are not normalizing, either in the sense of coming up with a relational design or in the sense of higher normal forms. You're just improving the design.

score 0 · Answer 2 · answered Nov 22 '16 at 14:44

thanks to @Viki888 I guess I resolved the query

INSERT INTO `person-color`
SELECT i.id `i.id`, c.id `c.id`
FROM (
  SELECT `p`.`id`, `ptemp`.`color1` `pcolor`
    FROM `person` `p`
      JOIN `person_temp` `ptemp` ON `p`.`name` = `ptemp`.`name`
    UNION
        SELECT `p`.`id`, `ptemp`.`color2` FROM `person` `p`
            JOIN `person_temp` `ptemp` ON `p`.`name` = `ptemp`.`name`
    UNION
        SELECT `p`.`id`, `ptemp`.`color3` FROM `person` `p`
            JOIN `person_temp` `ptemp` ON `p`.`name` = `ptemp`.`name`
    UNION
        SELECT `p`.`id`, `ptemp`.`color4` FROM `person` `p`
            JOIN `person_temp` `ptemp` ON `p`.`name` = `ptemp`.`name`
  ORDER BY `id`
) i
  JOIN color c
    ON c.name = i.pcolor
ORDER BY `i.id`

score 0 · Answer 3 · answered Nov 23 '16 at 02:20

For what it's worth, I wouldn't bother to do this in such a clever way just to do it in one query. There's no benefit to doing that.

It's simpler to write the code to do this in several passes, and simpler means you get the job done quicker.

INSERT INTO `person-color` (person_id, color_id)
SELECT p.id, c.id
FROM person p 
JOIN person_temp pt ON p.name=pt.name
JOIN color c ON pt.color1=c.name;

INSERT INTO `person-color` (person_id, color_id)
SELECT p.id, c.id
FROM person p 
JOIN person_temp pt ON p.name=pt.name
JOIN color c ON pt.color2=c.name;

INSERT INTO `person-color` (person_id, color_id)
SELECT p.id, c.id
FROM person p 
JOIN person_temp pt ON p.name=pt.name
JOIN color c ON pt.color3=c.name;

INSERT INTO `person-color` (person_id, color_id)
SELECT p.id, c.id
FROM person p 
JOIN person_temp pt ON p.name=pt.name
JOIN color c ON pt.color4=c.name;

There's no need to use UNION, or ORDER BY.

Populate many-to-many person-color table using three tables in MySQL

3 Answers3

Linked