0

I have 2 tables Product & Product_BOM () where Product is the master table and Product_BOM contains the parts ( bill of materials ) of the products. Please note that a PART (BOM) is another Product.

Table Product

Product_id,Product_name,Product_type ..

Table Product_BOM

Product_Bom_ID, Product_id

on the above tables Product.Product_id = Product_BOM.Product_id

As you can see that a BOM is another product and it may have many other Products as BOM This can be any deeper level. Now the problem is that when the main product is sold i want to decrease the quantity of its BOM (in all its deeper levels and i cant assume the depth). If i have only 2 levels then i can easily JOIN with same table to achieve the result. 

SELECT .....   
FROM
Product P //Main Product
LEFT JOIN Product_BOM BOM on BOM.Product_id = P.Product_id // Get its BOM
JOIN Product pLevel1 on pLevel1.Product_id = BOM.Product_id // Get BOM's Product name etc

A basic diagram follows

CAR (Main Product)
    ---TYRE (BOM of LEVEL 1)
    -------RUBBER (BOM of LEVEL 2)
    ---SEAT (BOM of LEVEL 1)
    -------LEATHER(BOM of LEVEL 2)
    ---ENGINE (BOM of LEVEL 1)
    -------RPC-AM1(BOM of LEVEL 2)
    ----------R-18(BOM of LEVEL 3)
    ----------R-19(BOM of LEVEL 3)
    -------CQR-DDF1.0(BOM of LEVEL 2)

On the above all the PART's of the CAR is another Product. Now i have the parent product how i get all the child items belongs to it in any deeper level ?

Is there any other way to achieve the same without JOIN ?

Red
  • 6,230
  • 12
  • 65
  • 112
  • This sounds like you either need to use recursive procedures (messy) or to redesign you tables to use the nested set method. http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – Kickstart Nov 04 '13 at 09:51
  • @Kickstart I cannot change the table structure. However what is nested set method ? – Red Nov 04 '13 at 09:59
  • 1
    Essentially each parent has a left and a right value which bracket the children under it. So rubber would have the values 3 and 4. While Tyre would have the values 2 and 5 (ie bracketing 3 and 4). So you can spot any child as its left and right values will be between the parents left and right values. There are tutorials on line which can explain this far better than I can. – Kickstart Nov 04 '13 at 10:08
  • I refereed some articles and feels good. I can understand why left/right. But i couldnt find a way to implement on my situation. On your example what is 3 & 4 ? – Red Nov 04 '13 at 11:20
  • The left and right for 3 and 4 are next to each other as that item has no child records. It enables you to count the number of children of a record easily. The big down side of this is inserts / deletes can result in having to update most records on the table – Kickstart Nov 04 '13 at 12:37

0 Answers0