Bug #61446 mysql tree
Submitted: 8 Jun 2011 15:49 Modified: 8 Jun 2011 16:06
Reporter: Mohan T Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: MySQL, mysql tree

[8 Jun 2011 15:49] Mohan T
Description:
i have table category,

mysql> desc category;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id           | int(11)     | NO   | PRI | NULL    |       |
| product_name | varchar(45) | YES  |     | NULL    |       |
| parent_id    | int(11)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec)

mysql> select * from category;
+----+--------------+-----------+
| id | product_name | parent_id |
+----+--------------+-----------+
|  1 | Television   |      NULL |
|  2 | LG           |         1 |
|  3 | Samsung      |         1 |
|  4 | Sony         |         1 |
|  5 | Laptop       |      NULL |
|  6 | DELL         |         5 |
|  7 | HP           |         5 |
|  8 | LG-Flat      |         2 |
|  9 | DELL-INP15   |         6 |
+----+--------------+-----------+
9 rows in set (0.00 sec)

now i need to fetch all the child node when id is given,
i wrote an procudure which fetch's the id correctly 
but problem here is, it can only fetch for existing value,
i.e suppose if an child_id is added under 9, i need to 
rewrite the query once again,

delimiter //
create procedure p1 (in id bigint)
    begin
    select t1.id as Parent, t2.id as Child, t3.id as Child_Node
    from category as t1
    left join category as t2 on t2.parent_id = t1.id
    left join category as t3 on t3.parent_id = t2.id
    where t1.id = (id);
    end //

delimiter ;
mysql> call p1(5) //
+--------+-------+------------+
| Parent | Child | Child_Node |
+--------+-------+------------+
|      5 |     6 |          9 |
|      5 |     7 |       NULL |
+--------+-------+------------+
2 rows in set (0.00 sec)

i need the same output for n number of child nodes,
please help to solve this., so that for ex, 
an child node 10 can be add under 9,
an child node 11 can be add under 10, so on..

i need to display child nodes for given id.,
the tree can have n number of child nodes.,

though this is not place to ask help,
i dont have any other option, 
i searched and worked through many procedures, function, cursor,
none found working.,

plz find me a solution., thank you 

How to repeat:

the table can have n number of child,
the id and child_id should be displayed for given id.,

plz dont suggest to go through SQL manual for last few days i read and searched a lot, over books and net., i can find solution only for fixed table !! 

Suggested fix:

it would be good if MYSQL comes up more efficient in loop's !!!
[8 Jun 2011 16:06] Miguel Solorzano
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.