Page 1 of 1
Recursive SQL to get Parent IDs
Posted: Wed Mar 28, 2007 11:54 pm
by muhammad_yahya
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.
Posted: Thu Mar 29, 2007 12:17 am
by dibyendrah
As this is a flat table, using query wouldn't make sense to fetch what you have been looking for. I think you have to rely on recursive function.
Posted: Thu Mar 29, 2007 12:25 am
by dibyendrah
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/
Posted: Thu Mar 29, 2007 10:33 am
by Begby
If you have a fixed maximum depth, say four levels deep, you can do this with one query
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
Or something like that, I am just taking this all from memory and don't have time to test it right now[/syntax]
Posted: Thu Mar 29, 2007 10:40 am
by feyd
Remember that with an increase in depth (and number of records to search) comes an exponential increase in processing requirements and quantity look ups. Be very careful when using self joins.
Posted: Thu Mar 29, 2007 3:19 pm
by mikeq
http://www.sitepoint.com/article/hierar ... a-database
this is a very good article on hierarchical trees in mysql
Posted: Thu Mar 29, 2007 6:28 pm
by aaronhall
Like feyd mentioned, dealing with self joins to pull hierarchical data is a slippery slope. You might fetch all of the data out of the table, and create a function to build an associative array to represent the data, and then a recursive function to display it.