Solved

Nested Query Problem

Posted on 2009-05-05
3
236 Views
Last Modified: 2012-05-06
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
Comment
Question by:bejhan
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 24309618
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
 
LVL 1

Author Comment

by:bejhan
ID: 24309635
Nice I didn't know you could just do that. Thanks!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24309654
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to join on ID, with prefix? 15 58
T-SQL Query to include null values 3 29
Run SQL Server Proc from Access 11 30
2 IIF's in Access query 25 25
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question