We have two production sites: Portland and Seattle. (Hint: look for Production tables) Create a combined list
Question:
We have two production sites: Portland and Seattle. (Hint: look for Production tables)
Create a combined list of all products from both sites, including at least the name, number, cost, and price of the products. And please sort it intelligently. (There should be 140 rows, not 128 rows!)
SELECT 'Seattle' AS ProductionSite, p.ProductID, p.Name, pc.StandardCost, sod.UnitPrice
FROM Production.Product AS p
JOIN Production.ProductCostHistory AS pc ON p.ProductID = pc.ProductID
JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE EXISTS (SELECT * FROM Production.ProductInventory AS pi WHERE p.ProductID = pi.ProductID AND pi.LocationID = 1)
union
SELECT 'Portland' AS ProductionSite, p.ProductID, p.Name, pc.StandardCost, sod.UnitPrice
FROM Production.Product AS p
JOIN Production.ProductCostHistory AS pc ON p.ProductID = pc.ProductID
JOIN Sales.SalesOrderDetail AS sod ON p.ProductID = sod.ProductID
WHERE EXISTS (SELECT * FROM Production.ProductInventory AS pi WHERE p.ProductID = pi.ProductID AND pi.LocationID = 1)