Link to home
Create AccountLog in
Avatar of ChoobsTech
ChoobsTechFlag for Switzerland

asked on

mysql recursive query

Dear experts,

I have a table with entities. They have parent child relations, i.e. so entity "dad" would be entity 1 have parent 0, and child "firstC" would have as parent "dad" and could have a child "first_first_child" and so on...

entities        |    entities_parent_child  |    
----------       |   ---------------------------   |  
identities     |   idparent                       |  
name           |   identity                        |  
---------        |  ----------------------------  |

Selecting first child of a parent would be:
SELECT *.e FROM entities AS e, entities_parent_child AS p WHERE e.identity=p.entities AND idparent = "XYZ_id"

How can I make a query getting all child of first child without using php?
I know it has to be something like:
SELECT *.e FROM entities AS e, entities_parent_child AS p WHERE e.identity=p.entities AND idparent = "XYZ_id"  OR e.identities= ANY (SELECT * FROM entities WHERE identities= "FIRST CHILD ID")

Can someone help out on this one?
Avatar of dqmq
dqmq
Flag of United States of America image

SELECT e3.*
FROM
   entities AS e1
, entities_parent_child AS c1
, entities_parent_child as c2
, entities as e3
WHERE e1.name = "XYZ"
and e1.identity=c1.idparent
and c1.identity=c2.idparent
and e3.identity=c2.identity


Avatar of ChoobsTech

ASKER

May be I just don't understand it...

But as far as I follow on this query: Nothing recursive goes on as I cannot get the value of field "e1" to get its childs in e3...

So it's just fetching 2 times same query, which btw is executed quite slowly...
It's not recursive.  As far as I know, MySQL does not have a recursive query feature.

The query returns the "grand children" of the e1.  In other words, the children of e1's children.  If that is not what you want, then please clarify your question, perhaps with some sample data.

To see the names at all levels, do like this:

SELECT e1.name as parent, e2.name as child,  e3.name as childofchild
FROM
   entities AS e1
, entities_parent_child AS c1
, entities as e2
, entities_parent_child as c2
, entities as e3
WHERE e1.name = 'YZ'
and e1.identity=c1.idparent
and c1.identity=c2.idparent
and e3.identity=c2.identity
and e2.identity=c1.identity
To check childs of "BOB" is used your query:
SELECT e1.name as parent, e2.name as child,  e3.name as childofchild
FROM
   entities AS e1
, entities_parent_child AS c1
, entities as e2
, entities_parent_child as c2
, entities as e3
WHERE e1.name = 'BOB'
and e1.identity=c1.idparent
and c1.identity=c2.idparent
and e3.identity=c2.identity
and e2.identity=c1.identity

It returns 3 times the parent "BOB" and I want to know childs he has...

What I would like is following in a single Mysql query:

let's say entity with id 525 has as name BOB wich has childs I wanna know the names of:

function get_childs($parent)
{
$q="SELECT identity FROM entity WHERE identity= ANY (SELECT identity FROM entities_parent_child WHERE idparent='525')";
$query=mysql($q);

 while ($row = mysql_fetch_array($query))
 {
echo "<table><tr><td>-</td></tr>";
  echo "<tr><td>entity name:".$row['name']."</td></tr>";
get_childs($row['identity']);
echo "</td></tr></table>";
 }
}

get_childs('525');

Will get me result:
BOB
-linda
--Nuria
---Brian
---Marcel
----Elvis
-susie
--Henry
-marcel

And it will get me childs of bob (linda, susie and marcel) and child of childs (linda has nuria as child) and childs 3rd genration (nuria is parent of Brian)

It will take all bod's decendance. I would like to have this into a single query to be able to use SQL features...




ASKER CERTIFIED SOLUTION
Avatar of dqmq
dqmq
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
No worries :D

I think I will open a new question with what I showed you last to make it clearer for other people and downgrade the points of current question to still award you for the help you provided and I appreciated.

Thanx mate ;)
Hope you don't get offended by the "C" grade... I think it is fair, but if you feel it's not I'm opened to your arguments :D
No offense taken.  I feel lucky to get any points, since I did not really satisfy your requirements.  When you rephrase the question, be sure to express your requirement to list a known enitity and ALL it's descendents.  That part was not clear in the original question--I thought you just wanted to show the names 2 generations removed.