I want to design a query to get the product and ingredients and the ingredients of the ingredients.
Products and Ingredients are in the same table. They can be differentiated by column LINE_TYPE and can be linked with the column FORMULA_ID.
The screenshot has been attached for more understanding in which you can see that in the first three records (Activity INSULATION) have one product which is formed by two ingredients.
Then in Activity 'STRANDING' Product is made which is one of the Ingredients of Activity Insulation.
This is how it keeps going until Activity 'Wire Drawing'.
We have to get ingredients and the ingredients of the ingredients until the activity is “Wire Drawing”.
product = "X"
Ingredients = "Y" , "Z"
"Y" is a product in other formula
and it's ingredients = " K " , " L "
and " L " could be product in other formula.