COALESCE henter alle data i kolonnen.
Hej eksperter.Jeg er igang med at lave en webshop, og skal bruge en ordreliste. Jeg har 2 tabeller til mine ordre:
tblOrders
OrderID
OrderDate
OrderSentDate
Name
Address
ZipCode
City
Phone
Email
Total
OrderID
OrderDate
OrderSentDate
Name
Address
ZipCode
City
Phone
Total
tblOrderLines
OrderLineID
OrderID
ProductID
Quantity
Price
OrderLineID
OrderID
ProductID
Quantity
Price
Lige pt. forsøger jeg med denne query:
DECLARE @Products varchar(MAX)
DECLARE @Quantity varchar(100)
DECLARE @Prices varchar(MAX)
DECLARE @Total money
SELECT @Products = COALESCE(@Products + ',<br/>', '') + CAST(tblProducts.ProductName AS varchar),
@Quantity = COALESCE(@Quantity + '<br/>', '') + CAST(tblOrderLines.Quantity AS varchar),
@Prices = COALESCE(@Prices + '<br/>', '') + CAST(tblOrderLines.Price AS varchar),
@Total = tblOrders.Total
FROM tblOrderLines
INNER JOIN tblProducts ON tblOrderLines.ProductID = tblProducts.ProductID
INNER JOIN tblOrders ON tblOrderLines.OrderID = tblOrders.OrderID
SELECT @Products AS Products, @Quantity AS Quantity, @Prices AS Prices, OrderID, OrderDate, Name, Address, ZipCode, City, Phone, Email, CONVERT(varchar,@Total,0)
FROM tblOrders
WHERE tblOrders.OrderSentDate IS NULL
DECLARE @Quantity varchar(100)
DECLARE @Prices varchar(MAX)
DECLARE @Total money
SELECT @Products = COALESCE(@Products + ',<br/>', '') + CAST(tblProducts.ProductName AS varchar),
@Quantity = COALESCE(@Quantity + '<br/>', '') + CAST(tblOrderLines.Quantity AS varchar),
@Prices = COALESCE(@Prices + '<br/>', '') + CAST(tblOrderLines.Price AS varchar),
@Total = tblOrders.Total
FROM tblOrderLines
INNER JOIN tblProducts ON tblOrderLines.ProductID = tblProducts.ProductID
INNER JOIN tblOrders ON tblOrderLines.OrderID = tblOrders.OrderID
SELECT @Products AS Products, @Quantity AS Quantity, @Prices AS Prices, OrderID, OrderDate, Name, Address, ZipCode, City, Phone, Email, CONVERT(varchar,@Total,0)
FROM tblOrders
WHERE tblOrders.OrderSentDate IS NULL
Men den henter alle produkter i tblOrderLines, istedet for for den med det rette OrderID. Ved at hardcode mit ID, får jeg det rigtige resultat, men jeg kan ikke lige fikse så den fungerer ordenligt. Når jeg hardcoder ID'et ind, sætter jeg denne WHERE-clause ind efter mine INNER JOIN's.
WHERE tblOrderLines.OrderID = 1
Og får dette resultat:
Products
TestProduct1<br/>TestProduct 2
Quantity
2<br/>1
Price
10<br/>45
TestProduct1<br/>TestProduct 2
Quantity
2<br/>1
Price
10<br/>45
Og uden WHERE-clause'et bliver de andre produkter, antal og pris som er i tblOrderLines, tilføjet.
Jeg leder derfor efter en løsning som kan regne det rigtige ID ud.
Håber i kan hjælpe :)
