Recursive Query with MySQL ? - GoFuckYourself.com

qw12er · 03-16-2011, 12:55 PM

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

redwhiteandblue · 03-16-2011, 01:00 PM

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

modF · 03-16-2011, 01:05 PM

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

qw12er · 03-16-2011, 01:08 PM

Quote:

Originally Posted by modF

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

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

KillerK · 03-16-2011, 03:00 PM

http://stackoverflow.com/questions/3...ql-with-clause

donborno · 03-16-2011, 04:06 PM

Google Nested set model

Tempest · 03-16-2011, 06:58 PM

Quote:

Originally Posted by qw12er

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

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

qw12er · 03-16-2011, 08:43 PM

Quote:

Originally Posted by donborno

Google Nested set model

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

vdbucks · 03-16-2011, 08:57 PM

Quote:

Originally Posted by qw12er

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

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:

Code:

idParent* = 1
|
---> idChild => 2,3,4,5,6 #so on and so forth

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.

sheken · 03-16-2011, 09:07 PM

Quote:

Originally Posted by donborno

Google Nested set model

^^

this

Tempest · 03-16-2011, 09:12 PM

Quote:

Originally Posted by vdbucks

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:

Code:

idParent* = 1
|
---> idChild => 2,3,4,5,6 #so on and so forth

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.

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.

qw12er · 03-16-2011, 09:29 PM

Quote:

Originally Posted by Tempest

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)

vdbucks · 03-16-2011, 09:59 PM

Quote:

Originally Posted by Tempest

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.

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?

qw12er · 03-16-2011, 10:01 PM

Quote:

Originally Posted by vdbucks

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

vdbucks · 03-16-2011, 10:21 PM

Quote:

Originally Posted by qw12er

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

Quote:

<?php
$idChild = 6 //however you get idChild value

// create a query first in the event you have to do things with the query like mysql_real_escape_string and whatnot
$query = sprintf("SELECT idParent, idChild FROM dbtable WHERE idChild = '%s'");

//process the query
$result = mysql_query($query);

while ($row = mysql_fetch_assoc($result)) {
// do stuff
echo $row['idChild'];
echo $row['idParent'];
}
?>

vdbucks · 03-16-2011, 10:52 PM

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'];
}

qw12er · 03-17-2011, 07:56 AM

Quote:

Originally Posted by vdbucks

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

Can it be a simple matter of say...

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)

HarryMuff · 03-17-2011, 08:00 AM

Derp de derp, ta teetley tum

donborno · 03-17-2011, 11:53 AM

Quote:

Originally Posted by qw12er

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

Which is very simple using the nested set model

03-16-2011, 12:55 PM	#1
qw12er Confirmed User Join Date: Apr 2004 Location: Montreal Posts: 799	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 __________________ I have nothing to advertise ... yet.

03-16-2011, 01:00 PM	#2
redwhiteandblue Bollocks Industry Role: Join Date: Jun 2007 Location: Bollocks Posts: 2,792	Think you would need a stored procedure for that, but not sure as I've never done one in MySQL. __________________ Interserver unmanaged AMD Ryzen servers from $73.00

03-16-2011, 01:05 PM	#3
modF Confirmed User Join Date: Aug 2002 Posts: 1,888	Last I checked, the WITH clause was not supported in MySQL. __________________ I do things skype:themodF

03-16-2011, 03:00 PM	#5
KillerK Confirmed User Join Date: May 2008 Posts: 3,406	http://stackoverflow.com/questions/3...ql-with-clause

03-16-2011, 04:06 PM	#6
donborno Confirmed User Join Date: Jan 2007 Location: Vienna, Austria Posts: 374	Google Nested set model

03-16-2011, 10:52 PM	#16
vdbucks Monger Cash Industry Role: Join Date: Jul 2010 Posts: 2,773	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']; }

03-17-2011, 08:00 AM	#18
HarryMuff Confirmed User Join Date: Dec 2005 Posts: 271	Derp de derp, ta teetley tum