Combining Known Queries

user_3521652 0
Viewed 54

A company sells Widgets that they store in several warehouses:. So I have these tables:

+----------+-------------+--------+------+
| WidgetID | Description | Price  | .... |
+----------+-------------+--------+------+
|        1 | Red         | 123.00 |      |
|        2 | Blue        | 321.00 |      |
|        3 | ....        |   .... |      |
|     .... |             |        |      |
+----------+-------------+--------+------+

and

+-------------+----------+------+
| WarehouseID | Location | .... |
+-------------+----------+------+
|           1 | Chicago  |      |
|           2 | Seattle  |      |
|           3 | ....     | .... |
|        .... |          |      |
+-------------+----------+------+

and

+-------------+----------+-------+
| WarehouseID | WidgetID | Count |
+-------------+----------+-------+
|           1 |        3 |    18 |
|           1 |        6 |    33 |
|           2 |       44 |   100 |
|           2 |       26 |     6 |
+-------------+----------+-------+

They sell Packages which contain various combinations of widgets, so I have this table which defines each package:

+-----------+----------+-------+
| PackageID | WidgetID | Count |
+-----------+----------+-------+
|         1 |        9 |     7 |
|         1 |       66 |    28 |
|         1 |        2 |    50 |
|         2 |        9 |    44 |
+-----------+----------+-------+

Now I know how to get how many of each type of widget they have:

SELECT
    WidgetID, SUM([Count]) AS Available
FROM
    WidgetLocations
GROUP BY
    WidgetID;

and I have this query to get the widget requirements for each package:

SELECT
    Packages.PackageID,
    Packages.Description,
    Packages.Cost,
    PackageContents.WidgetID,
    PackageContents.[Count] AS Required
FROM
    Packages LEFT OUTER JOIN PackageContents ON Packages.PackageID = PackageContents.PackageID

What I can't figure out is how to combine these two queries to get the following result:

+-----------+----------+----------+-----------+
| PackageID | WidgetID | Required | Available |
+-----------+----------+----------+-----------+

It would be ideal if another query could show the Widget that limits the production of more of each package:

+-----------+------------+-------------------+-------------+
| PackageID | Production | Limiting WidgetID | Requirement |
+-----------+------------+-------------------+-------------+

where

  • Production is the number of packages that can be made up from existing inventory
  • Limiting widget is the one that has the smallest requirement of additional widgets to be able to make more packages
  • Requirement is that quantity.

Unfortunately, that query is beyond my current SQL skills.

sql-server sql
1 Answers

For your first query, that is quite simple: you just need to join WidgetLocations already grouped up. You can do this directly in the SELECT with a correlated subquery, but then you can only return one result. Better to do this as a join or an apply, I prefer option 3, an apply:

SELECT
    p.PackageID,
    pc.WidgetID,
    pc.[Count] AS Required,
    (SELECT
        SUM(wl.[Count])
      FROM
        WidgetLocations wl
      WHERE wl.WidgetID = pc.WidgetID
    ) AS Available
    wl2.Available,
    wl3.Available
FROM
    Packages p
JOIN PackageContents pc ON p.PackageID = pc.PackageID
-- option 2
JOIN (
    SELECT
        wl.WidgetID,
        SUM(wl.[Count]) AS Available
    FROM
        WidgetLocations wl
) wl2 ON wl2.WidgetID = pc.WidgetID
-- option 3
CROSS APPLY (
    SELECT
        SUM(wl.[Count]) AS Available
    FROM
        WidgetLocations wl
    WHERE wl.WidgetID = pc.WidgetID
) wl3

For your second query, that is more complex. To do this with joins, you would need to group up by Widget and get the Available and Required, then group again by Package and take the Widget with the lowest number (this would need row-numbering).

Again it is much easier with an apply instead. We just need to use SELECT TOP 1 to get the correct Widget:

SELECT
    p.PackageID,
    pc.Available / pc.Requirement as Production,
    pc.WidgetID AS [Limiting WidgetID],
    pc.Requirement
FROM
    Packages p
CROSS APPLY (
    SELECT TOP (1)
        pc.WidgetID,
        pc.[Count] AS Requirement,
        SUM(wl.[Count]) AS Available
    FROM
        PackageContents pc
    JOIN
        WidgetLocations wl ON wl.WidgetID = pc.WidgetID
    WHERE
        p.PackageID = pc.PackageID
    GROUP BY
        pc.WidgetID,
        pc.[Count]
    ORDER BY
        SUM(wl.[Count]) / pc.[Count]
) pc
user_148689970
Related