How to retrieve hierarchical data stored in mysql using sql-query

Question

My Database structure is like follows

id category    parent
-------------------------
1  Programming 
2  Database    Programming
3  MySQL       Database

Now when i query for MySQL, i should get the result as

MySQL -> Database -> Programming

I received some suggestions that, i can do it using Stored Procedures. But it will make my query slow.

see this: mikehillyer.com/articles/managing-hierarchical-data-in-mysql — Omesh
– Omesh, Commented Aug 24, 2012 at 5:23
Omesh, your link was much helpful. It solved my purpose for now. But when the application grows, the number of category grows, it may slow down my query because for each depth, i need a join. — Swadesh Behera
– Swadesh Behera, Commented Aug 24, 2012 at 5:32

s.webbandit · Accepted Answer · 2012-08-24 05:23:41Z

1

This is just what JOINs are made for.

answered Aug 24, 2012 at 5:23

s.webbandit

17.2k17 gold badges60 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandy8086 · Accepted Answer · 2012-08-24 05:42:13Z

0

means you want to display output

Database -> Programming

this format.

I tried one query

 select category,"->",parent from table where id =2;

it gave output

category ->  parent
Database -> Programming

edited Aug 24, 2012 at 5:42

answered Aug 24, 2012 at 5:33

Sandy8086

6431 gold badge5 silver badges14 bronze badges

2 Comments

Swadesh Behera Over a year ago

Thanks Sandy, But i need it further. The output should be MySQL -> Database -> Programming (instead of MySQL -> Database)

Sandy8086 Over a year ago

try this one select "MySQL","->",category,"->",parent from table where id =2;

swapnesh · Accepted Answer · 2012-08-24 06:12:03Z

0

Check this query and let me know if it works or not( testtube is my table name )---

SELECT a.category,  "->", a.parent,  "->", b.parent
FROM testtube AS a
INNER JOIN testtube AS b
WHERE a.parent = b.category
AND a.category =  "MySql"
LIMIT 0 , 30

answered Aug 24, 2012 at 6:12

swapnesh

26.8k24 gold badges97 silver badges129 bronze badges

Collectives™ on Stack Overflow

How to retrieve hierarchical data stored in mysql using sql-query

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related