Sql query select by parent id

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

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating