bejhan
asked on
Nested Query Problem
The nested COUNT query does not recognize a.CustomerID, it comes up asking for that. What am I doing wrong?
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType)
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity, b.NumberOfOtherItems, a.DebtType
FROM ltblAllItems a, (SELECT COUNT(*) - 1 AS NumberOfOtherItems FROM ltblAllItems c WHERE a.CustomerID = c.CustomerID) AS b
WHERE a.Balance = 0
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could have done like this:
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType)
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity
, b.NumberOfOtherItems
, a.DebtType
FROM ltblAllItems a
INNER JOIN ( SELECT CustomerID, COUNT(*) - 1 AS NumberOfOtherItems
FROM ltblAllItems c
GROUP BY CustomerID
) b
ON (a.CustomerID = c.CustomerID)
WHERE a.Balance = 0
ASKER