SQL create category path/tree/hierachy

Question

This is not a duplicate question as the above link simply creates the same problem i am trying to answer

I have a simple table in phpmyadmin consisting of category id, the name of the category and its parent category id, thus the columns are labeled: id,name,parentid

Example data:

id  name    parentid
1   animal  NULL
2   vegetable   NULL
3   mineral NULL
4   doggie  1
5   kittie  1
6   horsie  1
7   gerbil  1
8   birdie  1
9   carrot  2
10  tomato  2
11  potato  2
12  celery  2
13  rutabaga    2
14  quartz  3
15  feldspar    3
16  silica  3
17  gypsum  3
18  hunting 4
19  companion   4
20  herding 4
21  setter  18
22  pointer 18
23  terrier 18
24  poodle  19
25  chihuahua   19
26  shepherd    20
27  collie  20

I would like to output all category paths and the id of each. So far i can create the path with the following sql:

select root.name  as root_name
     , down1.name as down1_name
     , down2.name as down2_name
     , down3.name as down3_name
  from categories as root
left outer
  join categories as down1
    on down1.parentid = root.id
left outer
  join categories as down2
    on down2.parentid = down1.id
left outer
  join categories as down3
    on down3.parentid = down2.id
 where root.parentid is null
order 
    by root_name 
     , down1_name 
     , down2_name 
     , down3_name

All i want to add to the output is the ID of each category along with its path. I found the above code at: http://sqllessons.com/categories.html

The above code creates the following:

root_name   down1_name  down2_name  down3_name
animal  birdie  NULL    NULL
animal  doggie  companion   chihuahua
animal  doggie  companion   poodle
animal  doggie  herding collie
animal  doggie  herding shepherd
animal  doggie  hunting pointer
animal  doggie  hunting setter
animal  doggie  hunting terrier
animal  gerbil  NULL    NULL
animal  horsie  NULL    NULL
animal  kittie  NULL    NULL
mineral feldspar    NULL    NULL
mineral gypsum  NULL    NULL
mineral quartz  NULL    NULL
mineral silica  NULL    NULL
vegetable   carrot  NULL    NULL
vegetable   celery  NULL    NULL
vegetable   potato  NULL    NULL
vegetable   rutabaga    NULL    NULL
vegetable   tomato  NULL    NULL

However, i want it to create the following:

id  root_name   down1_name  down2_name  down3_name
8   animal  birdie  NULL    NULL
25  animal  doggie  companion   chihuahua
24  animal  doggie  companion   poodle
27  animal  doggie  herding collie
26  animal  doggie  herding shepherd
22  animal  doggie  hunting pointer
21  animal  doggie  hunting setter
23  animal  doggie  hunting terrier
7   animal  gerbil  NULL    NULL
6   animal  horsie  NULL    NULL
5   animal  kittie  NULL    NULL
15  mineral feldspar    NULL    NULL
17  mineral gypsum  NULL    NULL
14  mineral quartz  NULL    NULL
16  mineral silica  NULL    NULL
9   vegetable   carrot  NULL    NULL
12  vegetable   celery  NULL    NULL
11  vegetable   potato  NULL    NULL
13  vegetable   rutabaga    NULL    NULL
10  vegetable   tomato  NULL    NULL

Baring in mind that the above is from the above link and the the real data could contain upto 6 levels of subcategories deep.

Thanks for your help

John

possible duplicate of [MySQL - Recursing a tree structure](http://stackoverflow.com/questions/2378678/mysql-recursing-a-tree-structure) — Jeremy Smyth, Sep 17 '13 at 12:37
Hi, its not a duplicate as those answers still only produce what i already have. Where i just need to figure out a way to add to that the id of the category that is producing each path in each row — user2231688, Sep 17 '13 at 12:45
@user2231688 - just curious but what is the advantage of this in the way you have lsited? You would always need to know and set the maximum depth of your table. I suppose if you only ever wanted to go down 4 levels then no problem but seems like there may be other ways. — JM4, Oct 02 '13 at 17:56

Wietze314 · Accepted Answer · 2013-09-18T07:09:23.533

1

select coalesce(down3.Id, down2.Id, down1.Id, root.Id), root.name  as root_name
 , down1.name as down1_name
 , down2.name as down2_name
 , down3.name as down3_name

edited Sep 18 '13 at 07:09

answered Sep 17 '13 at 15:23

Wietze314

5,942
2
21
40

I have already tried this, and it just gives the root id, whereas i want it to give me the id of the uppermost category in each line. Please see my above example with the bit that i want it to do. Thanks anyway – user2231688 Sep 17 '13 at 15:59
This worked perfectly. Thanks very much, would have never known to use coalesce – user2231688 Sep 18 '13 at 09:24

SQL create category path/tree/hierachy

1 Answers1