I don't know how to title this because I'm not sure about how to solve it or if it can be solved.
I have a list of products made of different components and I'd like to know how many I can produce taking in count these components can be produced through some other components.
Let's say this is my table of product structures:
CODE COMPONENT NEEDED
PROD1 BLISTER 1
PROD1 BOX 1
PROD1 TOY 1
TOY RIGHT ARM 1
TOY LEFT ARM 1
TOY EYE 2
I have this stock:
CODE STOCK
PROD1 100
BLISTER 100
BOX 50
TOY 15
RIGHT ARM 20
LEFT ARM 20
EYE 40
With this subselect I know how many I can produce of each code:
(SELECT min(isnull(STK.STOCK/STR.NEEDED,0))
from STRUCTURE STR
left join STOCKWAREHOUSE STK
on STR.COMPONENT = STK.CODE
where STR.CODE = ART.CODE) as PRODUCIBLE
Now it shows something like this:
CODE PRODUCIBLE
PROD1 15
TOY 20
I don't know how could I take in count the components I can produce as well (like the Toy). I think I should join that table twice, connected to itself, but I don't know how I can sum each component with the producible amount of that component.
The desired result is this:
CODE PRODUCIBLE
PROD1 35
TOY 20
EDIT:
Thanks to the answer I managed to sum both producibles, but it's not working as expected. Let's say a box is made of these components:
COMPONENT STOCK PRODUCIBLE
TOY1 400 150
TOY2 150 100
TOY3 0 100
TOY4 100 10
Now, it calculates the minimum producible for the box at the first level: 0 (because of the TOY3), and the minimum producible at the second level: 10 (because of TOY4), and then it sums both: 0+10. I would need to sum the stock with the producible, returning 100.
Ask me if I need to clarify anything. Thank you very much!
EDIT2: Nvm, fixed by calling the component from main table and summing its stock.
This looks like a business logic so I think you should not be doing this in the DB level.
Below query should give you your desired output for the data that your provided.
First subquery is what your provided, second subquery looks into the same output but at a component level. Finally you are adding both producible on the select.
select a.code1 as Code , a.p1 + isnull(c.p,0) as PRODUCIBLE
from
(
SELECT STR.CODE code1, min(isnull(STK.STOCK/STR.NEEDED,0)) p1
from STRUCTURE STR
left join STOCK STK
on STR.COMPONENT = STK.CODE
GROUP BY STR.CODE
) as a
full outer join
(
select s.CODE code, code component, b.p
from STRUCTURE s
join
(SELECT STR.CODE code, min(isnull(STK.STOCK/STR.NEEDED,0)) p
from STRUCTURE STR
left join STOCK STK
on STR.COMPONENT = STK.CODE
GROUP BY STR.CODE
) as b on b.code = s.COMPONENT
) as c on a.code1 = c.code