I need select data from table like this (its just example).
I want select data in format like Category1,Category2,Category3 without root by parent_id
| id_category | name | parent_id |
|---|---|---|
| 1 | root | 1 |
| 2 | Graphic card | 1 |
| 3 | Memory | 1 |
| 4 | DDR3 | 3 |
| 5 | Corsair | 4 |
| 6 | HyperX | 4 |
The result should be
| category1 | category2 | category3 |
|---|---|---|
| GraphicCard | null | null |
| Memory | DDR3 | Corsair |
| Memory | DDR3 | HyperX |
CodePudding user response：
This behaviour could be achieved using self joins. This should do the trick:
select a.name as category1, b.name as category2, c.name as category3
from yourtable a
left join yourtable b on a.id_category = b.parent_id
left join yourtable c on b.id_category = c.parent_id
where a.id_category in (2,3);