Help with SubQueries

[MichaelaLee]

Hi All,
I have recently converted a access database to SQL Server 2000. Everything has been going well, but I can't get one query I need to work. I suspect I need to do it another way. I hope You all can help. Here is what I'm trying to do. I have 3 queries, the top level one uses the other two to gather data for the top level query to use. Here they are:
CREATE PROCEDURE [dbo].[nf_TotalsForAllOrders] AS
SELECT DISTINCT nf_ShowProducts.ProgramName, nf_ShowProducts.ProductName, nf_SubTotalItems.SumOfQuantity,
nf_ShowProducts.OrderDate
FROM nf_ShowProducts INNER JOIN nf_SubTotalItems ON nf_ShowProducts.ProductID= nf_SubTotalItems.ProductID
ORDER BY nf_ShowProducts.ProgramName
GO

CREATE PROCEDURE [dbo].[nf_ShowProducts] AS
SELECT Programs.ProgramID, Programs.ProgramName, Orders.OrderID, Orders.ProgramID, Orders.OrderDate,
OrderDetail.Quantity, OrderDetail.ProductID, Products.ProductName
FROM ((Programs INNER JOIN Orders ON Programs.ProgramID = Orders.ProgramID) INNER JOIN OrderDetail
ON Orders.OrderID = OrderDetail.OrderID) INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID;
GO

CREATE PROCEDURE [dbo].[nf_SubTotalItems] AS
SELECT Products.ProductID, Sum(OrderDetail.Quantity) AS SumOfQuantity
FROM OrderDetail INNER JOIN Products ON OrderDetail.ProductID=Products.ProductID
GROUP BY Products.ProductID
GO

Is it possible to do this in SQL Server 2000. Thanks for any help possible.
Michael Lee

 

[whatduh00]

SQL is not like "exactly" like Access where you can base queries off of other queries. But what you can do is combine these three steps into one procedure.

How I see it you have a couple choices. First, is to build all these as one big select with subqueries. Like a so...

CREATE PROCEDURE [dbo].[nf_TotalsForAllOrders] AS

SELECT DISTINCT nf_ShowProducts.ProgramName, nf_ShowProducts.ProductName, nf_SubTotalItems.SumOfQuantity, 
nf_ShowProducts.OrderDate
FROM 
	(SELECT Programs.ProgramID, Programs.ProgramName, Orders.OrderID, Orders.ProgramID, Orders.OrderDate, 
	OrderDetail.Quantity, OrderDetail.ProductID, Products.ProductName
	FROM ((Programs INNER JOIN Orders ON Programs.ProgramID = Orders.ProgramID) INNER JOIN OrderDetail 
	ON Orders.OrderID = OrderDetail.OrderID) INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID) as nf_ShowProducts 
INNER JOIN 
	(SELECT Products.ProductID, Sum(OrderDetail.Quantity) AS SumOfQuantity
	FROM OrderDetail INNER JOIN Products ON OrderDetail.ProductID=Products.ProductID
	GROUP BY Products.ProductID) as nf_SubTotalItems 
ON nf_ShowProducts.ProductID= nf_SubTotalItems.ProductID
ORDER BY nf_ShowProducts.ProgramName
GO

A cleaner way to do it is to use temp tables. Its a little bit more taxing on the server but not anything noticable unless your working with a ton of records. Not sure how much sql experience you have but temp tables are pretty easy to work with. Just do a little searchin in the books online.

 

[ESquared]

I would say that SQL server IS exactly like Access in that you can base queries on other queries, they just have a different name: views.

In my opinion, there's no need to use a temp table. Cleanliness can be had with a little formatting work.


-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.