We help IT Professionals succeed at work.

New podcast episode! Our very own Community Manager, Rob Jurd, gives his insight on the value of an online community. Listen Now!

x

mysql recursive query

4,931 Views
Last Modified: 2013-12-13
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?
Comment
Watch Question

Commented:
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


Author

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

Commented:
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

Author

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




Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Author

Commented:
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

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.