MYSQL return parent & child

Question

Ok so I have a table called categories that looks like this:

id       name      parent_id
1        camera    null
2        computer  null
3        digital   1
4        gps       null
5        laptop    2

I want to be able to return computer -> laptop or camera -> digital when I query id = 3 or 5.

Any ideas?

score 2 · Answer 1 · edited May 23 '17 at 12:27

2

Try Nestedsets for those Structurs. It has a very good performance for selecting, but a bad performance for writing. So if you have much more Selects, as Insert or Updates (Structure Updates) on your Table its a good choice to use nested sets.

Breadcrumps with Nested Sets to get Category 1 -> SubCategory -> SubCategory

edited May 23 '17 at 12:27

Community

1
1

answered May 14 '12 at 11:28

take

2,202
2
19
36

All my CS lectures are flooding back! Will have a proper read of this and implement if I decide that the structure will contain more then two levels. Thanks – nick May 14 '12 at 14:06

score 0 · Answer 2 · answered May 14 '12 at 11:29

0

if it's fixed column count of results set

select p.name, c.name
mytable c, mytable p
where c.parent_id = p.id
and c.id in (3,5)

answered May 14 '12 at 11:29

triclosan

5,578
6
26
50

score 0 · Answer 3 · answered May 14 '12 at 11:32

0

What you are trying to use is an anti-pattern. If you continue using it you may encoutner some problems when you will want to select entire trees of subcategories. Look at take's answer for proper pattern.

answered May 14 '12 at 11:32

RandomWhiteTrash

3,974
5
29
42

score 0 · Answer 4 · answered May 14 '12 at 11:33

0

select t2.name + "->" + t1.name 
from categories t1
inner join t2 on t1.id = t2.parent_id
where t1.id in (3, 5)

answered May 14 '12 at 11:33

inser

1,190
8
17

Pablo · Answer 5 · 2012-05-14T11:41:36.157

0

If you only have one level of parent - child references:

SELECT IF(p.name IS NOT NULL, p.name + " -> " + a.name, a.name) AS name
FROM categories a
LEFT JOIN categories p ON a.parent_id = p.id

edited May 14 '12 at 11:41

answered May 14 '12 at 11:34

Pablo

3,655
2
30
44

score 0 · Accepted Answer · answered May 14 '12 at 11:34

Use a self left join :

select a.name, b.name as parent_name from categories a left join categories b on a.parent_id = b.id where a.id in (3,5);

Above query is valid for getting a one level of parent. If you want to traverse through the whole lineage please refer the following link. They have listed down few standard ways of traversing tree :

http://jan.kneschke.de/projects/mysql/sp/

MYSQL return parent & child

6 Answers6