|
Following are Microsoft SQL Server T-SQL examples for a multi-table JOINs:
USE NorthWind;
SELECT FullName=e.FirstName + ' ' + e.LastName,
TerritoryDescription,
RegionDescription
FROM Employees e
INNER JOIN EmployeeTerritories et
ON e.employeeid = et.employeeid
INNER JOIN Territories t
ON et.territoryid = t.territoryid
INNER JOIN Region r
ON t.regionid = r.regionid
ORDER BY TerritoryDescription,
RegionDescription
/*
FullName TerritoryDescription RegionDescription
Janet Leverling Atlanta Southern
Laura Callahan Beachwood Northern
Andrew Fuller Bedford Eastern
Michael Suyama Bellevue Western
.....
*/
USE AdventureWorks;
DECLARE @Year int, @Month int
SET @Year = 2004; SET @Month=1;
SELECT SOH.SalesOrderNumber AS SON,
SOH.PurchaseOrderNumber AS PO,
S.Name AS Store,
Convert(VARCHAR,SOH.OrderDate,110) AS OrderDate,
Convert(VARCHAR,SOH.ShipDate,110) AS ShipDate,
'$' + Convert(VARCHAR,TotalDue,1) AS [Total Due],
C.FirstName + ' ' + C.LastName AS SalesStaff,
SM.Name AS ShpngMethod,
BA.AddressLine1 AS BlngAddress1,
Isnull(BA.AddressLine2,'') AS BlngAddress2,
BA.City AS BlngCity,
BSP.Name AS BlngStateProvince,
BA.PostalCode AS BlngPostalCode,
BCR.Name AS BlngCountryRegion,
SA.AddressLine1 AS ShpngAddress1,
Isnull(SA.AddressLine2,'') AS ShpngAddress2,
SA.City AS ShpngCity,
SSP.Name AS ShpngStateProvince,
SA.PostalCode AS ShpngPostalCode,
SCR.Name AS ShpngCountryRegion,
CC.FirstName + ' ' + CC.LastName AS CustomerContact,
CC.Phone AS CustomerPhone,
SOH.AccountNumber
FROM Person.Address SA
INNER JOIN Person.StateProvince SSP
ON SA.StateProvinceID = SSP.StateProvinceID
INNER JOIN Person.CountryRegion SCR
ON SSP.CountryRegionCode = SCR.CountryRegionCode
INNER JOIN Sales.SalesOrderHeader SOH
INNER JOIN Person.Contact CC
ON SOH.ContactID = CC.ContactID
INNER JOIN Person.Address BA
INNER JOIN Person.StateProvince BSP
ON BA.StateProvinceID = BSP.StateProvinceID
INNER JOIN Person.CountryRegion BCR
ON BSP.CountryRegionCode = BCR.CountryRegionCode
ON SOH.BillToAddressID = BA.AddressID
ON SA.AddressID = SOH.ShipToAddressID
INNER JOIN Person.Contact C
INNER JOIN HumanResources.Employee E
ON C.ContactID = E.ContactID
ON SOH.SalesPersonID = E.EmployeeID
INNER JOIN Purchasing.ShipMethod SM
ON SOH.ShipMethodID = SM.ShipMethodID
INNER JOIN Sales.Store S
ON SOH.CustomerID = S.CustomerID
WHERE Year(SOH.OrderDate) = @Year
AND Month(SOH.OrderDate) = @Month
ORDER BY Store,
OrderDate DESC
GO
/*
SON PO Store OrderDate
SO61257 PO3741176337 Activity Center 01-01-2004
SO61256 PO1421187796 All Cycle Shop 01-01-2004
SO61251 PO6380165323 All Seasons Sports Supply 01-01-2004
SO61263 PO5452121402 Amalgamated Parts Shop 01-01-2004
....
*/
|
