Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
need sql query

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.
Answers
-
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
-
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)