Calculating values from three related tables, without using join or union 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.
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