如何从同一个表中获取相关行？

Question

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 1

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