Hi,
Thank you very much for you help. I did go through the code from your publication. My previous code look similar with your. My previous code work just fine, except....
Actually, I am trying to calculate the stock qty balance for my POS system. I need to deduct the stock qty in my stock_status table for those Assorted Pack (Multiple item packed in a single product, with multiple level), since our design only deduct the ingredient of assorted pack when sales, instead of doing packing.
My SQL work fine if the qty is correct, in the sennse that, the current ingredient's qty are enough for the quantity used by it's parent/assorted pack. However, Once either one of the assorted pack's qty in any level is not correct (run out, become ZERO value or not enough for it's parent ,if any) The qty if it's ingredient qty will not be deducted. I found that, recursive will solve this problem, but, i am looking for a solution that not using recursive, .. may be SQL as what you mentioned.
Any suggestion to my problem?? Please don't hestitate to ask me if you are not so clear about my english and problem.

Thank in advance.