Please help

I have a certain problem that I'm going to try and demonstrate using the northwind
database as an example.

I'm trying to write a stored procedure in SQL Server 2000, I am using the products table and
the order details table.

The first procedure I wrote uses a left outer join to show all the products and if the product
doesn't show up in the order details table it will show up with a zero no problem the stored procedure
is as follows and works great.

ALTER PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName

Now I change the stored procedure to filter the second table by an orderid and now the only products
that show up are the products that exist in the second table.

My question is how do I show all the products like in the first stored procedure
and filter the stored procedures second table and still show all the products
including the ones that don't show up.

I wrote the second stored procedure and like I said it only shows the products that show up in the second table.

ALTER PROCEDURE dbo.StoredProcedure2
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

HAVING (dbo.[Order Details].OrderID = 10248)

I would really appreciate if somebody codes demonstrate to me how to do this
thanks in advance.This is how it hsould be:

SELECT dbo.Products.ProductID, dbo.Products.ProductName,COUNT(dbo.[Order Details].ProductID)AS[in orders]
FROM dbo.ProductsLEFTOUTERJOIN
dbo.[Order Details]ON dbo.Products.ProductID= dbo.[Order Details].ProductID
WHERE dbo.[Order Details].OrderID= 10248
GROUPBY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

|||Thank you very much for your quick response ndinakar,
but the procedure you wrote gives the same result as the second procedure
I wrote.

What I wanted to do was show all the products that exist in the products table
like in the first procedure which shows all the products and products that don't show up
in the second table show up with a zero I would like to do the same thing but when I filter
the second table by the orderId the only result I get is the products that show up in the second table not all the products.

How can I show all the products from the products table
and still filter the productID in the second table|||Of course it only shows products from the second table. Your WHERE clause tells it to only show specific products from the second table.

Your question makes little sense...|||I showed have explained what I was trying to do in the first place,

I've written a program in ASP.NEV VB I have a drop-down list in a data grid
the drop-down list contains instructors names and the value contains their ID

The drop-down list data source is the instructors table each time and instructor makes a dive
it goes into the orders table by the instructors ID.

What I was trying to do is get a list of all the instructors and their ID and see how many times they show up
in the orders table for certain dates and arrange the drop-down list according to the amount of dive's
the instructor made during that date.

----------Instructors table----------

Inst_Idint40
Inst_Full_Namenvarchar500
Activebit10

-------Orders Table --------------

Order_Idint40
Activity_Idint40
Inst_Idint41
Client_Namenvarchar500
Telnvarchar301
Paymentint40
Voucher_Nonvarchar501
Agent_Idint41
Reservation_Noint41
Worker_Idint41
Order_Date_Timedatetime80
Equipmentnvarchar2|||Try something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, ISNULL(COUNT(dbo.[Order Details].ProductID),0 AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)

This will give you a 0 value for products with no corresponding orders.

Does this help, or am I still misunderstanding the requirements?|||OK, I see what I was missing. That OrderDate requirement.

No problem. Just do your OUTER JOIN to an inline table (or subquery).

LEFT OUTER JOIN
 (SELECT ProductID, DetailID
 FROM [Order Details]
 WHERE OrderDate = @.OrderDate) AS OrderDetails

Now just treat your virtual OrderDetails table the same as you would your real OrderDetails table. The rest of the query should be the same.|||I didn't exactly understand how to do it I am quite new to SQL Server
I tried copying your script like so

CREATE PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails

GO

But it did not work i probably don't understand how to make an inline table
if you could just give me a little more help thanks a lot|||SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(OrderDetails.DetailID) AS InOrders
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)|||I tried using the procedure you wrote and then tried it with my actual table
and each time I get the same error incorrect Syntex neither the keywords 'group'
do you have any suggestions but I've also attached my actual procedure.

---------------------------

ALTER PROCEDURE dbo.GetInstructors
(@.Id int)
AS

SELECT dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name, COUNT(dbo.Orders_Sub.Inst_Id) AS Expr1
FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
GROUP BY dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name
ORDER BY (3)

---------------------------

I really appreciate the help you giving me thank you in advance again.|||Sorry. I left out the join condition:

FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
ON dbo.Instructors.Inst_Id = Orders_Sub.Inst_Id

|||Thank you very much for your help it works like a dream.