Hi,
I have a table having fields (ID, Name, ParentID).
Sample Data is:
--------------------------------
ID - Name - ParentID
--------------------------------
1 One 0
2 Tow 1
3 Three 1
4 Four 2
---------------------------------
I need a SQL Query to get relust like this:
1
1 -> 2
1 -> 3
1 -> 2 -> 4
Any body can help.
Recursive SQL to get Parent IDs
Moderator: General Moderators
-
muhammad_yahya
- Forum Newbie
- Posts: 3
- Joined: Thu Mar 08, 2007 11:12 pm
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact:
Hello !
I have found a few links which deals with your problems and it has solutions too. Hope this will help you.
http://dev.mysql.com/tech-resources/art ... -data.html
http://www.hawkee.com/snippet/406/
I have found a few links which deals with your problems and it has solutions too. Hope this will help you.
http://dev.mysql.com/tech-resources/art ... -data.html
http://www.hawkee.com/snippet/406/
If you have a fixed maximum depth, say four levels deep, you can do this with one query
Or something like that, I am just taking this all from memory and don't have time to test it right now[/syntax]
Code: Select all
SELECT
t1.id as lvl1,
t2.id as lvl2,
t3.id as lvl3,
t4.id as lvl4
FROM
tblname t1
LEFT JOIN
tblname t2 ON t1.id = t2.parentID
LEFT JOIN
tblname t3 on t2.id = t3.parentID
LEFT JOIN
tblname t4 ON t4.id = t3.parentID
WHERE
t1.parentID = 0
http://www.sitepoint.com/article/hierar ... a-database
this is a very good article on hierarchical trees in mysql
this is a very good article on hierarchical trees in mysql