|
Execute the following
Microsoft SQL Server T-SQL scripts in SSMS Query Editor or Query Analyzer to get a report on all orders with 10%
(0.1) discount. The queries demonstrate INNER JOIN GROUP BY with derived table and CTE forms.
-- SQL INNER JOIN with SELECT GROUP BY query - derived table
-- SQL sequence numbering groups
USE Northwind
SELECT o.OrderID,
SeqNo,
ProductName,
o.UnitPrice,
o.Quantity,
Amount = o.UnitPrice * o.Quantity,
Discount = convert(DECIMAL(3,2),Discount)
FROM Products p
INNER JOIN [Order Details] o
ON p.ProductID = o.ProductID
INNER JOIN (SELECT count(* ) AS SeqNo,
a.OrderID,
a.ProductID
FROM [Order Details] a
JOIN [Order Details] b
ON a.ProductID >= b.ProductID
AND a.OrderID = b.OrderID
GROUP BY a.OrderID,
a.ProductID) seq
ON o.OrderID = seq.OrderID
AND o.ProductID = seq.ProductID
WHERE o.Discount = 0.1
ORDER BY o.OrderID
GO
/* Partial results
OrderID SeqNo ProductName UnitPrice
10248 1 Queso Cabrales 14.00
10248 2 Singaporean Hokkien Fried Mee 9.80
10248 3 Mozzarella di Giovanni 34.80
10249 1 Tofu 18.60
10249 2 Manjimup Dried Apples 42.40
10250 1 Jack's New England Clam Chowder 7.70
10250 2 Manjimup Dried Apples 42.40
*/
-- SQL INNER JOIN with SELECT GROUP BY query - CTE
;WITH CTE AS
(SELECT count(* ) AS SeqNo,
a.OrderID,
a.ProductID
FROM [Order Details] a
JOIN [Order Details] b
ON a.ProductID >= b.ProductID
AND a.OrderID = b.OrderID
GROUP BY a.OrderID,
a.ProductID)
SELECT o.OrderID,
SeqNo,
ProductName,
o.UnitPrice,
o.Quantity,
Amount = o.UnitPrice * o.Quantity,
Discount = convert(DECIMAL(3,2),Discount)
FROM Products p
INNER JOIN [Order Details] o
ON p.ProductID = o.ProductID
INNER JOIN CTE
ON o.OrderID = CTE.OrderID
AND o.ProductID = CTE.ProductID
WHERE o.Discount = 0.1
ORDER BY o.OrderID
GO
|
