Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate the discovery of relationship among data items in the same table by the application of self-joins.

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

-- SQL self-join QUICK SYNTAX - find products with same color

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

USE AdventureWorks2008;

SELECT Color=p1.Color, ProductA=p1.Name, ProductB=p2.Name

FROM Production.Product p1

  INNER JOIN Production.Product p2

    ON p1.Color = p2.Color                     -- Join on related property

       AND p1.Color is not null                -- Exlude no color products

       AND p1.ProductNumber < p2.ProductNumber -- Exclude duplicates

ORDER BY Color, ProductA, ProductB

GO

/* Color    ProductA                      ProductB

....

Multi       AWC Logo Cap                  Long-Sleeve Logo Jersey, L

Multi       AWC Logo Cap                  Long-Sleeve Logo Jersey, M

Multi       AWC Logo Cap                  Long-Sleeve Logo Jersey, S

Multi       AWC Logo Cap                  Long-Sleeve Logo Jersey, XL

Multi       AWC Logo Cap                  Men's Bib-Shorts, L

Multi       AWC Logo Cap                  Men's Bib-Shorts, M

Multi       AWC Logo Cap                  Men's Bib-Shorts, S

Multi       Long-Sleeve Logo Jersey, L    Long-Sleeve Logo Jersey, M

Multi       Long-Sleeve Logo Jersey, L    Long-Sleeve Logo Jersey, S

.....    */
------------

------------
-- SQL self-join example - staff reporting to the same supervisor
-- Using GetAncestor() HierarchyID system function - tree processing - orgchart

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

SELECT Supervisor = e1.OrganizationNode.GetAncestor(1),

       Staff1 = e1.OrganizationNode,

       Staff2 = e2.OrganizationNode

FROM   AdventureWorks2008.HumanResources.Employee e1

       INNER JOIN AdventureWorks2008.HumanResources.Employee e2

         ON e1.OrganizationNode.GetAncestor(1) = e2.OrganizationNode.GetAncestor(1)

            AND e1.BusinessEntityID < e2.BusinessEntityID

ORDER BY Supervisor, Staff1, Staff2

/* Supervisor     Staff1      Staff2

      0x          0x58        0x68

      0x          0x58        0x78

      0x          0x58        0x84

      0x          0x58        0x8C

      0x          0x58        0x94

      0x          0x68        0x78

      0x          0x68        0x84

....   */

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

------------
-- SQL self join - SQL Server joins - Find all the authors who live in the same city
------------
USE pubs
GO
SELECT   FirstAuthor = au1.au_fname + ' ' + au1.au_lname,
         SecondAuthor = au2.au_fname + ' ' + au2.au_lname,
         FirstCity=au1.city, SecondCity=au2.city
FROM     authors au1
         INNER JOIN authors au2
           ON au1.city = au2.city
WHERE    au1.au_fname + ' ' + au1.au_lname < au2.au_fname + ' ' + au2.au_lname
ORDER BY FirstAuthor, SecondAuthor
GO
/* Results
 

*/
------------
-- SQL self join: direct report query on self-referencing (ReportsTo) table
------------
USE Northwind;
GO
SELECT   COALESCE(m.LastName + ', ' + m.FirstName,'') AS 'Manager',
         e.LastName + ', ' + e.FirstName              AS 'Employee'
FROM     Employees AS e
         LEFT OUTER JOIN Employees AS m
           ON e.ReportsTo = m.EmployeeID
ORDER BY Manager,
         Employee
GO
/* Results
 
Manager                 Employee
                        Fuller, Andrew  -- CEO
Buchanan, Steven        Dodsworth, Anne
Buchanan, Steven        King, Robert
Buchanan, Steven        Suyama, Michael
Fuller, Andrew          Buchanan, Steven
Fuller, Andrew          Callahan, Laura
Fuller, Andrew          Davolio, Nancy
Fuller, Andrew          Leverling, Janet
Fuller, Andrew          Peacock, Margaret

*/

------------
-- FIND VENDORS SUPPLYING SAME PRODUCTS
------------
-- SQL self-join of tables with alias PV1 and PV2
-- INNER JOIN of PV1 & PV2 is a SELF-JOIN
USE ADVENTUREWORKS;
GO
SELECT   DISTINCT PRODUCTNAME = P.NAME,
                  VENDORNAME = V1.NAME
FROM     PURCHASING.PRODUCTVENDOR PV1
         INNER JOIN PURCHASING.PRODUCTVENDOR PV2
           ON PV1.PRODUCTID = PV2.PRODUCTID
              AND PV1.VENDORID <> PV2.VENDORID
         INNER JOIN PURCHASING.VENDOR V1
           ON V1.VENDORID = PV1.VENDORID
         INNER JOIN PRODUCTION.PRODUCT P
           ON P.PRODUCTID = PV1.PRODUCTID
ORDER BY PRODUCTNAME,
         VENDORNAME
GO
/* Partial results
 
ProductName             VendorName
Thin-Jam Lock Nut 9     Australia Bike Retailer
Thin-Jam Lock Nut 9     Leaf River Terrain
Thin-Jam Lock Nut 9     Ready Rentals
Touring Rim             Anderson's Custom Bikes
Touring Rim             Premier Sport, Inc.
Touring Tire            Professional Athletic Consultants
Touring Tire            Victory Bikes
 
*/
 
------------

Related article - Using Self-Joins:

http://msdn.microsoft.com/en-us/library/ms177490.aspx