DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to construct a four table INNER JOIN SELECT?

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

....

*/

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE