| How to
use derived tables to build complex queries? |
|
The following
samples demonstrates the application of derived table (cnum) to
build complex queries:
USE Northwind
SELECT c.CategoryName, p.ProductName, p.UnitPrice,
cnum.NoOfProducts
FROM Categories c
INNER JOIN Products p
ON c.CategoryID = p.CategoryID
INNER JOIN ( SELECT c.CategoryID, NoOfProducts = count(*)
FROM Categories c
INNER JOIN Products p
ON c.CategoryID = p.CategoryID
GROUP BY c.CategoryID) cnum
ON c.CategoryID = cnum.CategoryID
ORDER BY c.CategoryName
|
| The World Leader
in SQL Server Training |
|