Search code examples
sql-serversubquery

How to connect two or more subqueries or subselects


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.


Solution

  • 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