0

EDIT: After reading some of your answers i noticed i don't need recursion but some queries to work with the tree. I'm currently reading the following post http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ which is giving the basic concepts i didn't knew and the proper way to go. So thanks a lot, i'll keep on reading the posted links :)

i'm working on a tree where every node can have childs or not. Every node has its own auto generated node_id by the childs to reference its parent. A simple table example could be the following: 

CREATE TABLE IF NOT EXISTS `arbres` (
  `node_id` int(11) NOT NULL AUTO_INCREMENT,
  `pare_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`node_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=470 ;
ALTER TABLE arbres ADD CONSTRAINT FK_E6A4F221A4467B16 FOREIGN KEY (pare_id) REFERENCES arbres(node_id) ON DELETE CASCADE;

What i'm trying is to find a query which is able to select every child and so on, i mean the childs of the childs, the childs of the childs of the childs, etc in a single query. I'm very new to sql so i could accomplish it with multiples sql sentences which is not a good idea. Thx for your time.

peris
  • 943
  • 3
  • 20
  • 33
  • 1
    Maybe this will get you started: http://stackoverflow.com/q/1323245/535275 – Scott Hunter Jan 15 '12 at 11:58
  • 1
    MySQL does not support recursive queries. You will either need to create a stored procedure that does it, change your data model or "upgrade" to a DBMS that supports recursive queries such as PostgreSQL or Firebird (or any of the commercial ones) –  Jan 15 '12 at 11:58
  • 1
    Check out the following answer http://stackoverflow.com/questions/5291054/hierarchical-sql-problem/5291159#5291159 – Jon Black Jan 15 '12 at 12:04

1 Answers1

0

You best bet is to traverse the tree on a node basis. Since there is no garanty how many levels your tree would have, trying to find one query is probably not very good practice. There is nothing wrong with having one query or procedure which returns the children for a given parent multiple times like:

select node_id from arbres where pare_id = 1

That is assuming your root is pare_id =1 Then do the same thing for every node returned. If no node is returned then you know your last node was a leaf.

If you really need to achieve all this in one query simply do 

select * from arbres

And reconstrut the tree in which ever programming language you are using.

emt14
  • 4,846
  • 7
  • 37
  • 58
  • "*trying to find one query is probably not very good practice*". Note that this is only true for MySQL. Other DBMS make it extremely easy (and efficient) to do this kind of things in a single query –  Jan 15 '12 at 12:08
  • I must say that I have never used recursive query features, but only nested queries which I don't think would be suitable in the case. – emt14 Jan 15 '12 at 12:33
  • check out these http://stackoverflow.com/questions/3307480/postgresql-recursive-with http://stackoverflow.com/questions/584904/cte-to-traverse-back-up-a-hierarchy –  Jan 15 '12 at 12:37