quote:

---

Originally posted by Apples

Basically I have a simple site where a user enters their information and they order a pizza. I then have a page with a GridView where an Admin can look at all the orders. Here's my stored procedures:


sproc_ViewOrders
-------------------------------------------------------
ALTER PROCEDURE sproc_ViewOrders
AS
SELECT tblCustomers.FName + ' ' + tblCustomers.LName AS Name,
tblCustomers.Address + ' ' + tblCustomers.City + ', ' + tblCustomers.State + ' ' + tblCustomers.Zip AS Address,
'1 ' + tblSizes.SizeName + ' ' + tblCrusts.Crust + ': ' + dbo.sproc_ViewAllToppings(tblOrders.OrderID) AS [Order]
FROM tblCustomers INNER JOIN
tblOrders ON tblCustomers.CustID = tblOrders.CustID INNER JOIN
tblSizes ON tblOrders.SizeID = tblSizes.SizeID INNER JOIN
tblCrusts ON tblOrders.CrustID = tblCrusts.CrustID INNER JOIN
tblOrderToppings ON tblOrders.OrderID = tblOrderToppings.OrderID INNER JOIN
tblToppings ON tblOrderToppings.ToppingID = tblToppings.ToppingIDRETURN
--------------------------------------------------------------------------------------


sproc_ViewAllToppings
---------------------------------------------------------------
ALTER FUNCTION sproc_ViewAllToppings(@orderid tinyint)

RETURNS VARCHAR(1000)

AS

BEGIN
DECLARE @ToppingsList varchar(1000)

SELECT @ToppingsList = COALESCE(@ToppingsList + ', ', '') + tblToppings.Topping
FROM tblToppings INNER JOIN tblOrderToppings
ON tblToppings.ToppingID = tblOrderToppings.ToppingID
WHERE (@orderid = tblOrderToppings.OrderID)

RETURN @ToppingsList
END
-------------------------------------------------------------------------------------

The GridView should have the following format:
[FName] [LName] | [Street] [City] [State] [Zip] | 1 [Size] [Crust] : [List of Toppings]

For example:
John Doe | 1234 Main St. Dallas, TX 99999 | 1 Large Original : Pepperoni, Sausage

The only problem is that for how ever many toppings are on a person's pizza, that's how many times they are listed in the GridView. So for John Doe, he would be listed twice. I tried using DISTINCT after my SELECT statement in sproc_ViewOrders, but the problem with that is if someone makes the exact same order, the new order isn't listed in the GridView. I suspect the problem is with the JOIN statements.

---