need sql query

0614

hi,

im doing calculation in hierarchical way below are my details

CREATE TABLE `nodes_1` (

  `parent` int(11) DEFAULT NULL,

  `child` int(11) DEFAULT NULL,

  `marks` int(11) DEFAULT NULL

)

data:-

in a query if i pass 5 as parent that calculate 2,3 child marks

and the same if i pass 15 as parent that should calculate 10,5 marks --5 already having 2,3 child. Result would be 200

same if i pass most parent 38 it should calculate 15,17,6 child marks --15 already holding 200 marks ,17 has two childs 9,8 marks ,6 no marks --result 230 e.t.c

thanks in advance.

Dave Stokes-MySQL Community Team-Oracle

I am not sure what you mean by calculate marks (sum, average, etc,) or how you want the results broken down (parent, child, etc.).   You may have something as simple as 'select parent, child, sum(marks) from nodes_1 group by parent, child with rollup;' or something much more complex.

What are you trying to do?

Barbara Boehmer

MySQL does not have a lot of the functionality for recursive or hierarchical queries that Oracle does.  If you know the maximum number of levels, then you can query using one outer join per level, as shown below.

mysql> select * from nodes_1;
+--------+-------+-------+
| parent | child | marks |
+--------+-------+-------+
|      5 |     2 |    50 |
|      5 |     3 |    50 |
|     18 |    11 |     0 |
|     18 |     7 |     0 |
|     17 |     9 |    15 |
|     17 |     8 |    15 |
|     26 |    13 |     0 |
|     26 |     1 |     0 |
|     26 |    12 |     0 |
|     15 |    10 |   100 |
|     15 |     5 |     0 |
|     38 |    15 |     0 |
|     38 |    17 |     0 |
|     38 |     6 |     0 |
|   NULL |    38 |     0 |
|   NULL |    26 |     0 |
|   NULL |    18 |     0 |
+--------+-------+-------+
17 rows in set (0.00 sec)

mysql> select ifnull(sum(n1.marks),0) + ifnull(sum(n2.marks),0) + ifnull(sum(n3.marks),0) as total_marks
    -> from   nodes_1 n1 left outer join nodes_1 n2 on n1.child = n2.parent
    ->        left outer join nodes_1 n3 on n2.child = n3.parent
    -> where  n1.parent = 5
    -> group  by n1.parent;
+-------------+
| total_marks |
+-------------+
|         100 |
+-------------+
1 row in set (0.00 sec)

mysql> select ifnull(sum(n1.marks),0) + ifnull(sum(n2.marks),0) + ifnull(sum(n3.marks),0) as total_marks
    -> from   nodes_1 n1 left outer join nodes_1 n2 on n1.child = n2.parent
    ->        left outer join nodes_1 n3 on n2.child = n3.parent
    -> where  n1.parent = 15
    -> group  by n1.parent;
+-------------+
| total_marks |
+-------------+
|         200 |
+-------------+
1 row in set (0.00 sec)

mysql> select ifnull(sum(n1.marks),0) + ifnull(sum(n2.marks),0) + ifnull(sum(n3.marks),0) as total_marks
    -> from   nodes_1 n1 left outer join nodes_1 n2 on n1.child = n2.parent
    ->        left outer join nodes_1 n3 on n2.child = n3.parent
    -> where  n1.parent = 38
    -> group  by n1.parent;
+-------------+
| total_marks |
+-------------+
|         230 |
+-------------+
1 row in set (0.00 sec)