SQL Select where value = to another select I have two tables. Orders, and OrderParts. One Order can have many OrderParts.

I want a list of all the order.customerID where the number of OrderParts ordered = Total_number_parts in the order table.

Below is where i have got to so far, but where do i put the where sum(partqty)=total_number_parts bit!!

Select a.customerID, a.Total_number_parts
From Orders a
Join (Select sum(PartQty) from OrderParts Group by customerID) B
   on B.customerID = a.customerID
Group By a.CustomerID
You can use a CTE for this work:

WITH ctSum AS
(
    SELECT   customerId, SUM(PartQty) as TotalQty
    FROM     OrdersParts
    GROUP BY customerID
)
SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   ctSum b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;


Or if you prefer another syntax:

SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   (SELECT   customerId, SUM(PartQty) as TotalQty
        FROM     OrdersParts
        GROUP BY customerID) b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;