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 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to apply self-join for cross-row stats?

Execute the following SQL Server T-SQL script in Management Studio Query Editor to demonstrate the application of SELF-JOIN.

-- Find higher priced products with same color as products in a price range

-- SQL self-join - join table to itself - SQL inner join

SELECT p1.Color,

       Product1 = p1.Name,

       Price1 = p1.ListPrice,

       Product2 = p2.Name,

       Price2 = p2.ListPrice

FROM     Production.Product p1

         INNER JOIN Production.Product p2

           ON p1.Color = p2.Color

              AND p1.ProductID < p2.ProductID

              AND p1.ListPrice < p2.ListPrice

WHERE    p1.ListPrice BETWEEN 1000.0 AND 2000.0

ORDER BY Color,

         Product1,

         Product2

 

GO

/* Partial results

 

Color Product1                      Price1      Product2                Price2

Blue  HL Touring Frame - Blue, 60   1003.91     Touring-2000 Blue, 50   1214.85

Blue  HL Touring Frame - Blue, 60   1003.91     Touring-2000 Blue, 54   1214.85

Blue  HL Touring Frame - Blue, 60   1003.91     Touring-2000 Blue, 60   1214.85

Blue  Touring-2000 Blue, 60         1214.85     Touring-1000 Blue, 46   2384.07

Blue  Touring-2000 Blue, 60         1214.85     Touring-1000 Blue, 50   2384.07

Blue  Touring-2000 Blue, 60         1214.85     Touring-1000 Blue, 54   2384.07

Blue  Touring-2000 Blue, 60         1214.85     Touring-1000 Blue, 60   2384.07

Red   HL Road Frame - Red, 44       1431.50     Road-150 Red, 44        3578.27

Red   HL Road Frame - Red, 44       1431.50     Road-150 Red, 48        3578.27

Red   HL Road Frame - Red, 44       1431.50     Road-150 Red, 52        3578.27    

Red   HL Road Frame - Red, 44       1431.50     Road-150 Red, 56        3578.27

*/

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

-- Generate product (assembly) - vendor (supplier) association
-- SQL self-join

-- SQL inner join

USE AdventureWorks;

SELECT   DISTINCT PRODUCT = P.NAME,

                  VENDOR = V.NAME

FROM     PURCHASING.PRODUCTVENDOR PV1

         INNER JOIN PURCHASING.PRODUCTVENDOR PV2

           ON PV1.PRODUCTID = PV2.PRODUCTID

              AND PV1.VENDORID <> PV2.VENDORID

         INNER JOIN PURCHASING.VENDOR V

           ON V.VENDORID = PV1.VENDORID

         INNER JOIN PRODUCTION.PRODUCT P

           ON P.PRODUCTID = PV1.PRODUCTID

ORDER BY PRODUCT,

         VENDOR

GO

 

/* Partial results

 

PRODUCT                 VENDOR

LL Mountain Tire        Sport Fan Co.

LL Mountain Tire        Vista Road Bikes

LL Nipple               Lindell

LL Nipple               Northern Bike Travel

LL Road Pedal           Jackson Authority

LL Road Pedal           Mitchell Sports

LL Road Rim             Electronic Bike Repair & Supplies

LL Road Rim             International Bicycles

LL Road Seat/Saddle     Chicago City Saddles

LL Road Seat/Saddle     Hill's Bicycle Service

LL Road Tire            Professional Athletic Consultants

LL Road Tire            Signature Cycles

*/

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