BIData 201A TSQL Advanced: JOINS, UNIONS, and SUBQUERYS

Advanced JOINS

'-- Advanced Joins (Level 2) ------------------------------------------------------------------------------------------------'

      ---------------------------------------------------------------------

      -- T-SQL Fundamentals for Microsoft SQL Server 2012 and SQL Azure

      -- Chapter 03 - Joins

      -- © 2011 Itzik Ben-Gan

      -- Note: You must first run the TSQL2012.sql script to create the demo DB

      ---------------------------------------------------------------------

      'Joining to a Numbers table will force a LOOP to occur!

       This can be very handing for performing functions a set number of times!

       This example can be used to create such a table'

      -- All numbers from 1 - 1000

      -- Auxiliary table of digits

Auxiliary Tables

      USE TSQL2012;

      IF OBJECT_ID('dbo.Digits', 'U') IS NOT NULL DROP TABLE dbo.Digits;

      CREATE TABLE dbo.Digits(digit INT NOT NULL PRIMARY KEY);

      INSERT INTO dbo.Digits(digit)

        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

      SELECT digit FROM dbo.Digits;

      GO

      -- All numbers from 10 by 10

      SELECT D2.digit * 10

      FROM  dbo.Digits AS D1

      CROSS JOIN dbo.Digits AS D2;

      -- A thousand rows by ten

      SELECT D1.digit * 100 + D2.digit * 10

      FROM  dbo.Digits AS D1

      CROSS JOIN dbo.Digits AS D2

      ORDER BY 1;

-- All numbers from 1 - 1000

      -- Drop Table MyNumbersTable

        SELECT Top 1000 -- Need top to allow an Order By statement to process before the insert! -- RjR

          D3.digit * 100 + D2.digit * 10 + D1.digit + 1 as Col1

        into MyNumbersTable

        FROM  dbo.Digits AS D1

         CROSS JOIN dbo.Digits AS D2

         CROSS JOIN dbo.Digits AS D3

         ORDER BY Col1;

    GO

      Select * from MyNumbersTable; -- Otherwise the number are out of sequence.

COMPOSITE JOINS

---------------------------------------------------------------------

      -- Composite Joins

      ---------------------------------------------------------------------

      'Tables that audit changes to database data are another

       handy tool! Here is an example...'

      -- Audit table for updates against OrderDetails

      USE TSQL2012;

      IF OBJECT_ID('Sales.OrderDetailsAudit', 'U') IS NOT NULL

        DROP TABLE Sales.OrderDetailsAudit;

      CREATE TABLE Sales.OrderDetailsAudit

      (

        lsn        INT NOT NULL IDENTITY,

        orderid    INT NOT NULL,

        productid  INT NOT NULL,

        dt         DATETIME NOT NULL,

        loginname  sysname NOT NULL,

        columnname sysname NOT NULL,

        oldval     SQL_VARIANT,

        newval     SQL_VARIANT,

        CONSTRAINT PK_OrderDetailsAudit PRIMARY KEY(lsn),

        CONSTRAINT FK_OrderDetailsAudit_OrderDetails

             FOREIGN KEY(orderid, productid)

             REFERENCES Sales.OrderDetails(orderid, productid)

      );

      SELECT

        OD.orderid

      , OD.productid

      , OD.qty

      , ODA.dt

      , ODA.loginname, ODA.oldval, ODA.newval

      FROM Sales.OrderDetails AS OD

        JOIN Sales.OrderDetailsAudit AS ODA

             ON OD.orderid = ODA.orderid

             AND OD.productid = ODA.productid -- The AND keyword makes this a Composite!

      WHERE ODA.columnname = N'qty';