• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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

Open in new window

0
bejhan
Asked:
bejhan
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change to this:
INSERT INTO ltblPIFItems(CustomerID, BusinessUnit, ItemID, LastActivity, NumberOfOtherItems, DebtType) 
SELECT a.CustomerID, a.BusinessUnit, a.ItemID, a.LastActivity
, (SELECT COUNT(*) - 1 AS NumberOfOtherItems FROM ltblAllItems c WHERE a.CustomerID = c.CustomerID) 
, a.DebtType 
FROM ltblAllItems a
WHERE a.Balance = 0

Open in new window

0
 
bejhanAuthor Commented:
Nice I didn't know you could just do that. Thanks!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now