Using a current row value in a nested select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using a current row value in a nested select

Author

Topic

myksdsu
Starting Member

13 Posts

Posted - 2007-06-03 : 13:42:50

I have two tables:

1) table of customers: CustomerName, CustomerId, CustomerAddress
2) table of orders: OrderId, CustomerId, OrderAmount

I would like to have a query that returns everything from the customer table and add one column that has the amount of orders the customer has made, this is what I have so far:

CREATE PROCEDURE dbo.GetAllCutomerInfo
AS

DECLARE @OrderCount int

SELECT CustomerName, CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= CustomerId)
FROM Customers
ORDER BY CustomerName

Can you add a variable:

CREATE PROCEDURE dbo.GetAllCutomerInfo
AS

DECLARE @CustID int

DECLARE @OrderCount int

SELECT CustomerName, @CustID=CustomerId, CustomerAddress, (SELECT COUNT(OrderId) FROM Cust_Orders WHERE CustomerId= @CustID)
FROM Customers
ORDER BY CustomerName

Thanks for any help.

pootle_flump

1064 Posts

Posted - 2007-06-03 : 13:51:35

[code]SELECT dbo.Customers.CustomerName
, dbo.Customers.CustomerId
, dbo.Customers.CustomerAddress
, CustOrdCount.CustomerOrderCount
FROM dbo.Customers
LEFT OUTER JOIN --Customers' order counts
(SELECT CustomerId
, COUNT(OrderId) AS CustomerOrderCount
FROM dbo.Cust_Orders
GROUP BY CustomerId) AS CustOrdCount
ON CustOrdCount.CustomerId = dbo.Customers.CustomerId
WHERE dbo.Customers.CustomerId= @CustID
ORDER BY CustomerName[/code]