stack stack - 6 months ago 9
MySQL Question

How to get related row from same table?

I have a table like this:

// mytable
+----+---------+---------+
| id | related | color |
+----+---------+---------+
| 1 | 1 | red |
| 2 | 1 | blue |
| 3 | 3 | green |
| 4 | 1 | white |
| 5 | 3 | brown |
| 6 | 6 | 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
.

For example:

$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`


I can do that by using a
JOIN
. Something like this:

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


My query works as expected .. But I'm not sure using a
JOIN
for doing that is standard. Actually I'm trying to know is there any better approach? Or mine is a normal way?

Answer

What's wrong with SELECT t.related FROM mytable t WHERE t.id = :id? JOIN makes nothing more but checking if there is a actual id in 'related' column or not