Calculating values from three related tables, without using join or union

Question

I have a simple basic three tables on SQL Server database:

What is needed is to get the quantity available, without using joins or unions which is calculated:

Quantity Available = Items Opening Balance - Items Delivered + Items Received

Any Ideas are welcome.

Answer 1

It seems you are being asked to write the query using correlated subqueries, for example:

SELECT
    IOB.ItemID,
    IOB.Descript,
    IOB.Quantity +
    (
        -- Total received for this item
        SELECT ISNULL(SUM(IR.Quantity), 0)
        FROM dbo.ItemsReceived AS IR
        WHERE IR.ItemID = IOB.ItemID
    )
    -
    (
        -- Total delivered for this item
        SELECT TotalDelivered = ISNULL(SUM(ID.Quantity), 0)
        FROM dbo.ItemsDelivered AS ID
        WHERE ID.ItemID = IOB.ItemID
    )
FROM dbo.ItemsOpeningBalance AS IOB;

SQLFiddle Demo

If you're using SQL Server 2005 or later, you could also use APPLY (a lateral or correlated join) but I suspect this would be viewed as not in the spirit of the exercise. The following actually requires SQL Server 2008 or later, because it (gratuitously) uses GROUP BY ().

SELECT
    IOB.ItemID,
    IOB.Descript,
    QuantityAvailable =
        IOB.Quantity +
        ISNULL(ItemsReceived.Quantity, 0) -
        ISNULL(ItemsDelivered.Quantity, 0)
FROM dbo.ItemsOpeningBalance AS IOB
OUTER APPLY
(
    SELECT SUM(IR.Quantity)
    FROM dbo.ItemsReceived AS IR
    WHERE IR.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsReceived (Quantity)
OUTER APPLY 
(
    SELECT SUM(ID.Quantity)
    FROM dbo.ItemsDelivered AS ID
    WHERE ID.ItemID = IOB.ItemID
    GROUP BY ()
) AS ItemsDelivered (Quantity);

SQLFiddle Demo

Answer 2

A similar but different and much poorer approach to Paul's would be to compute the value as scalars and then do your calculation.

DECLARE
    @QuantityOpening int
,   @QuantityDelivered int
,   @QuantityReceived int
,   @ItemID int = 1;

WITH ItemsOpeningBalance(ItemID, Quantity) AS
(
    SELECT
    *
    FROM
    (
        VALUES
            (1, 10)
        ,   (2, 20)
    ) D(a,b)
)
,   ItemsDelivered(SalesOrderID, ItemID, Quantity) AS
(
    SELECT
    *
    FROM
    (
    VALUES
        (100, 1, 2)
    ,   (1000, 1, 3)
    ,   (200, 2, 100)
    ) D(a,b,c)
)
,   ItemsReceived(PurchaseOrderID, ItemID, Quantity) AS
(
    SELECT
    *
    FROM
    (
    VALUES
        (10000, 1, 5)
    ,   (20000, 2, 100)
    ) D(a,b,c)
)
-- Watch this pattern
SELECT
    @QuantityOpening = COALESCE(
(
    SELECT 
        SUM(IOB.Quantity) AS StartingQuantity
    FROM
        ItemsOpeningBalance AS IOB
    WHERE
        IOB.ItemID = @ItemID
),  0)
,  @QuantityDelivered = COALESCE(
(
    SELECT 
        SUM(ID.Quantity) AS StartingQuantity
    FROM
        ItemsDelivered AS ID
    WHERE
        ID.ItemID = @ItemID
), 0)
,@QuantityReceived = COALESCE(
(
    SELECT 
        SUM(IR.Quantity) AS StartingQuantity
    FROM
        ItemsReceived AS IR
    WHERE
        IR.ItemID = @ItemID
), 0);

SELECT
    @QuantityOpening - @QuantityDelivered +  @QuantityReceived AS TotalAmount;