|
Execute the following
SQL Server Transact-SQL scripts in Management Studio Query Editor to list the product details for English culture markets.
-- SQL inner join - SQL coalesce - SQL left join - left outer join
-- SQL column alias - SQL money format - currency format
USE AdventureWorks;
SELECT P.[Name] AS ProductName,
PM.[Name] AS ProductModel,
PC.[Name] AS ProductCategory,
PS.[Name] AS ProductSubCategory,
PD.DESCRIPTION,
P.ProductNumber,
COALESCE(P.Color,'') AS Color,
COALESCE(P.[Size], '') AS Size,
COALESCE(CAST(P.Weight AS varchar),'') Weight,
'$'+convert(varchar,P.StandardCost,1) AS StandardCost,
COALESCE(P.Style, '') AS Style,
COALESCE(P.Class, '') AS Class,
'$'+convert(varchar,P.ListPrice,1) AS ListPrice
FROM Production.Product P
INNER JOIN Production.ProductSubcategory PS
ON P.ProductSubcategoryID = PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PS.ProductCategoryID = PC.ProductCategoryID
INNER JOIN Production.ProductProductPhoto PPP
ON P.ProductID = PPP.ProductID
INNER JOIN Production.ProductPhoto PP
ON PPP.ProductPhotoID = PP.ProductPhotoID
LEFT OUTER JOIN Production.ProductDescription PD
INNER JOIN Production.ProductModelProductDescriptionCulture PCULT
ON PD.ProductDescriptionID = PCULT.ProductDescriptionID
INNER JOIN Production.ProductModel PM
ON PM.ProductModelID = PCULT.ProductModelID
ON P.ProductModelID = PM.ProductModelID
WHERE (PCULT.CultureID = 'en')
ORDER BY ProductName
GO
/* Partial results
ProductName ProductModel ProductCategory ProductSubCategory
All-Purpose Bike Stand All-Purpose Bike Stand Accessories Bike Stands
AWC Logo Cap Cycling Cap Clothing Caps
Bike Wash - Dissolver Bike Wash Accessories Cleaners
Cable Lock Cable Lock Accessories Locks
Chain Chain Components Chains
Classic Vest, L Classic Vest Clothing Vests
Classic Vest, M Classic Vest Clothing Vests
*/ |
