[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mysql recursive query

Posted on 2007-10-06
8
Medium Priority
?
4,897 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?
0
Comment
Question by:ChoobsTech
  • 4
  • 4
8 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 20029373
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


0
 
LVL 2

Author Comment

by:ChoobsTech
ID: 20030441
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...
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20030899
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:ChoobsTech
ID: 20031149
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...




0
 
LVL 42

Accepted Solution

by:
dqmq earned 1000 total points
ID: 20031247
My query is for 3 levels only:  parent, child, grandchild. It can be extended to support fewer or more levels, but the maximum number of levels is finite.  And the more levels you have, the worse performance will get.  I do not know how to do it recursively in MySQL.   Sorry.

0
 
LVL 2

Author Comment

by:ChoobsTech
ID: 20031250
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 ;)
0
 
LVL 2

Author Comment

by:ChoobsTech
ID: 20031253
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
0
 
LVL 42

Expert Comment

by:dqmq
ID: 20031309
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
While opting for any web-to-print solution, you need to discuss with your team and some of your end users and know their opinions about your decisions. In this article we list down some questions you need to ask yourself.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question