Page 1 of 1

Get resulting parent/child in a single query...?

Posted: Fri Mar 24, 2006 7:59 pm
by hawleyjr
I have a parent/child relationship in my table I'm trying to get all parents from a child in a single query. For example: The following table:

Code: Select all

child_id	|	parent_id
22		|	0
23		|	0
24		|	22
25		|	24
26		|	0
27		|	26
If I select by 25 I would like the resulting to be:

Code: Select all

25
24
22
24 being a parent of 25, 22 being a parent of 24 and 22 not having a parent.

I'm assuming this is going to be an inner join I'm just not sure how to recursively do it with mysql.

I can create a loop w/ php to do this however; I'd like to see if this can be done in a single query.

Posted: Fri Mar 24, 2006 8:46 pm
by John Cartwright
typically you store the parent id in the child row

Posted: Fri Mar 24, 2006 9:08 pm
by feyd
this sort of table structure isn't conducive to heirarchical data, as I've found. (I've been thinking too object oriented at times.) What I've been using recently, thanks to a thread is presorted trees which led to this: http://dev.mysql.com/tech-resources/art ... -data.html .. later there was a reference to basically the same thing on "the nameless place" sitepoint

Posted: Sat Mar 25, 2006 2:30 am
by timvw
Hmmz, remembers me of the fact that i still have that Joe Celko book lying around somewhere.. Meaby it's time to give it some caring this weekend... Anyway, if you where going to take the nested sets approach there is always http://pear.php.net/package/DB_NestedSet.