Hi Venu, all!
VenuGopal Papasani wrote:
Hi all,
I Have a table with followin structure where i have orgunit and parent
and value
orgunit parent value
12 10 x
15 12 y
16 12 z
17 12 p
Now here 15,16, and 17 are the children of 12 and 12 is the
child of 10.Now i need a query which gives the sum of all the children of
12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a
query which gives the sum of all these.It will be very helpful if i am able
to get it in a single query.
Sure there is:
SELECT SUM(value) FROM his_table WHERE (orgunit = 12 OR parent = 12)
This is fairly basic SQL, nothing tricky involved.
This does not descend into deeper hierarchies directly, for that you
would need a more elaborate statement.
If you have to deal with deeper hierarchies, you might search for the
"nested set model" which makes it easier to work on them than the
traditional "parent pointer" approach you have used here.
Sorry, the only URL I have ready is in German, which may not help you much.
HTH,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
