Recursive Query with MySQL ?
 

Basicaly I need to fetch the root parent of any object child. (could be as low as 25 level deep into the tree structure.)

Is there a way to do it in mysql ? I've seen it in SQLServer with the "With" statement.

heres my db columns:

dbID idParent idChild
1 2
1 3
2 4
2 5
4 6


For example, I need to query to return 1 if I look for idChild = 6

thanks

Think you would need a stored procedure for that, but not sure as I've never done one in MySQL.

Last I checked, the WITH clause was not supported in MySQL.

It's not ... and this is why I'm having trouble.

Google Nested set model

Can't tell what you're trying to accomplish with that table. Seems to be missing data.

Regardless, it sounds like you real problem is that the DB design sucks and you'd just have to do multiple queries..

Problem ain't speed ... it's getting root parent...

Not really sure what you're trying to accomplish as it doesn't appear as though your example matches your explanation.

Are you trying to do something like:

From the sounds of your description, this is what you mean.. but from your example it's not. But then, the example you gave doesn't show how the idChild 6 has the root idParent of 1 to begin with... Unless of course I'm missing something.

All in all, more information is needed.

*I'm not including additional idParent values as of now in order to get an exact idea of what you're trying to accomplish.

^^

this

I "think" what he's looking for is to keep checking for a parent until there are none... So if he starts with idChild = 6, it's parent is 4.. Then he checks child 4 exists and finds it has a parent of 2.. Then he checks child 2 and finds it exists with a parent of 1.. Then he checks child 1 and find there is no child so the root parent is 1.

Exactly !!

Sorry if my explanations weren't clear.
(The html kind of scrw up my table)

I figured this was what he was trying to do but didn't want to assume.. it appears as though you're right though from his reply to this..

@qw12er - Now let me ask.. is this in php?

php 5.3 -yup

Have you tried pulling up the entire query and using maybe a foreach or while loop?

Can it be a simple matter of say...

Additionally, if you just want to get all idChild values and match them with idParent values, you can modify the above $query to:

$query = sprintf('SELECT idParent, idChild FROM dbtable');

Then change the while line to:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
//do stuff
echo $row['idChild'];
echo $row['idParent'];
}

Yeah that's is sort of what I did but this solutions isn't clean as it could be.
(makes way to much query to DB for nothing)

Derp de derp, ta teetley tum

Which is very simple using the nested set model