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

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
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

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

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

typically you store the parent id in the child row
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

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