Recursive SQL to get Parent IDs

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
muhammad_yahya
Forum Newbie
Posts: 3
Joined: Thu Mar 08, 2007 11:12 pm

Recursive SQL to get Parent IDs

Post 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.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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/
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

http://www.sitepoint.com/article/hierar ... a-database

this is a very good article on hierarchical trees in mysql
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Post 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.
Post Reply