Subject nesting - weight calculation
Author checkmail



there is a table with all Material (stock), each one has a number. Now, in a second table there is listed the material that is a part of the Material.


For example:


Material ABC

Material BCD

Material XXX

Material OMV

Material KOM


Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD can assigned to XXX, OMV, ABC.


Now I would like to know what is the total weight of the main material, (total weight from abc + amount of bcd + amount of xxx


ABC has 2 XXX, XXX has 2 OMV

Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on


The structure


Table Mainmaterial




Table Material

PartNo of Mainmaterial

PartNoA = assigned Material

Amount (of PartNoA)


Is there a simpler statement possible than my stored procedure (recursive)


Thank you.