i want to run a query to get Hierarchy of catagory_name

Question

if my database look like this:-

| CATAGORY_ID | CATAGORY_NAME | PARENT_ID |
-------------------------------------------
|           1 |             x |         0 |
|           2 |             y |         1 |
|           3 |             z |         2 |

i used this to get one hirarchy but i want ds to run untill its get parent_id =0

SELECT catagory_name
FROM catagory_master
WHERE catagory_id = ( 
SELECT parent_id
FROM catagory_master
WHERE catagory_id =4 )

if i want x-y-z for catagory_id=3 and x-y for catagory_id=2 and x for catagory_id=1 please help i have to run this in php script

Short answer: you can't, in a single query. Longer answer here: [Using MySQL query to traverse rows to make a recursive tree](http://stackoverflow.com/questions/10646833/using-mysql-query-to-traverse-rows-to-make-a-recursive-tree) — , May 24 '13 at 06:47

Meherzad · Answer 1 · 2013-05-24T07:13:04.627

Try this query

select 
catagory_id,
catagory_name,
@pv:=parent_id as 'parent_id'
from (select * from table1 order by catagory_id desc)a
join
(select @pv:=3)tmp
where catagory_id=@pv

SQL FIDDLE:

| CATAGORY_ID | CATAGORY_NAME | PARENT_ID |
-------------------------------------------
|           3 |             z |         2 |
|           2 |             y |         1 |
|           1 |             x |         0 |

Note: This query will work if the parent_id is always less than the category_id of that record. If you will remove the order by desc field you will get only 1 record. If you want a proper concrete solution than you need a stored proc..

score 0 · Answer 2 · answered May 24 '13 at 06:44

0

you need to use IN for this if i get what you want!

SELECT catagory_name
FROM catagory_master
WHERE catagory_id IN ( 
    SELECT parent_id
    FROM catagory_master
    WHERE catagory_id = 4 )

answered May 24 '13 at 06:44

rcpayan

535
3
15

this will only print x but i want x-y-z – rahul singh May 24 '13 at 06:54

score 0 · Answer 3 · answered May 24 '13 at 06:57

If your database supports RECURSIVE queries, you can do something like this:

WITH RECURSIVE category_tree(id, name, path, level) AS (
        SELECT catagory_id, catagory_name, ARRAY[id], 0
    FROM catagory_master
    WHERE parent_id = 0
    UNION ALL
    SELECT catagory_master.catagory_id, catagory_master.catagory_name, path || catagory_master.catagory_id, level + 1
    FROM category_tree
    JOIN catagory_master ON catagory_master.parent_id = category_tree.catagory_id
    WHERE NOT catagory_master.catagory_id = ANY(path)
    )
    SELECT * FROM category_tree where 0 = ANY(path) ORDER BY path ;

And you should have something like this, that can be used to buid a tree:

enter image description here

MySQL doesn't support recursive queries. – May 24 '13 at 06:59 — , May 24 '13 at 06:59

i want to run a query to get Hierarchy of catagory_name

3 Answers3

SQL FIDDLE: