Forum Stats

  • 3,837,785 Users
  • 2,262,300 Discussions
  • 7,900,393 Comments

Discussions

Categories

need sql query

0614
0614 Member Posts: 121 Red Ribbon
edited Feb 7, 2017 5:34PM in MySQL Community Space

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

pastedImage_0.png

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.

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Feb 7, 2017 11:48AM

    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?

    Dave Stokes

    MySQL Community Manager

    · Share on TwitterShare on Facebook
  • Barbara Boehmer
    Barbara Boehmer Member Posts: 4,625 Gold Trophy
    edited Feb 7, 2017 5:34PM

    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)

    · Share on TwitterShare on Facebook