stack stack - 6 months ago 21
MySQL Question

How can I do a self-join based on a related column?

I have this table:

// mytable
+----+---------+---------+
| id | related | color |
+----+---------+---------+
| 1 | NULL | red |
| 2 | 1 | blue |
| 3 | NULL | green |
| 4 | 1 | white |
| 5 | 3 | brown |
| 6 | NULL | gray |
| 7 | 3 | black |
| 8 | 1 | orange |
| 9 | 6 | pink |
+----+---------+---------+


I have an
id
number and I need to get the color of related
id
.

Here is some examples:

$id = 4; // I need to get `red`
$id = 5; // I need to get `green`
$id = 6; // I need to get `gray`
$id = 9; // I need to get `gray`


And here is my query:

SELECT t2.color FROM mytable t1 JOIN mytable t2 ON t1.related = t2.id WHERE t1.id = :id


It works for almost all rows and it returns expected color. Just it doesn't work for those rows which have
NULL
related. How can I fix the problem?

Answer

You can use a LEFT JOIN to associate the related colors to the id in the related column. In cases where there is no related color, the COALESCE() will return the primary color.

SELECT t.id, COALESCE(r.color,t.color) AS color
FROM myTable t
LEFT OUTER JOIN myTable r ON r.id = t.related
WHERE t.id = :id

Here is the INNER JOIN query for comparison. You'll see that you lose the rows where the related id is NULL, which I don't believe is what you want.

SELECT t.id, COALESCE(r.color,t.color) AS color
FROM myTable t
INNER JOIN myTable r ON r.id = t.related
WHERE t.id = :id