Comparing an integer field in each record of a table to the sum of associated records in another table

Let's say I have these two tables: Product and Storage

Product has a ProductId, and a QuantityAvailable for each record

Storage has a LocationId, ProductId and QuantityAvailable for each record (ProductId is a FK to the product table). A 'ProductId' can be stored in multiple 'LocationId's, therefore ProductId and LocationId make up a compound primary key.

What I want is an SQL query that is able to determine if a 'Product' is in storage, and if so, if the 'Quantity' in cumulative storage is less than the 'QuantityAvailable'.

To put things simply, I want a listing of all the 'Pro开发者_如何学编程ductId's in the Product table where the sum of the 'Quantity' of all the associated entries in the Storage table is less than the 'QuantityAvailable' of the Product Table.

How can I achieve this (without changing the table structure)?

thanks

The following query will give you the sum of the quantities for each product:

select ProductID, sum(Quantity) as ProductQuantity
from
    Storage
group by
    ProductID

Now you simply need to use that query as a derived table, join to it, and compare your 2 quantities:

select
    *
from
    Product p
    inner join (
        select ProductID, sum(Quantity) as ProductQuantity
        from
            Storage
        group by
            ProductID) q
        on  p.ProductID = q.ProductID
where
    q.ProductQuantity < p.QuantityAvailable