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?

dqmq

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

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

dqmq

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

ChoobsTech

ASKER

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

dqmq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ChoobsTech

ASKER

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 ;)

ChoobsTech

ASKER

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

dqmq

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.