Execute the following SQL Server T-SQL script in Query Editor to demonstrate the use of self-join on the ProductVendor table to find products with multiple vendors:

USE AdventureWorks;

GO

SELECT DISTINCT

      ProductName=p.Name,

      ProductNumber,

      Vendor=v.Name

FROM Purchasing.ProductVendor pv1

-- This is a self-join

INNER JOIN Purchasing.ProductVendor pv2

      ON pv1.ProductID = pv2.ProductID

      AND pv1.VendorID != pv2.VendorID

INNER JOIN Production.Product p

      ON pv1.ProductID = p.ProductID

INNER JOIN Purchasing.Vendor v

      ON pv1.VendorID = v.VendorID

ORDER BY

      ProductName,

      Vendor

GO   

/*

ProductName       ProductNumber     Vendor

Chainring         CR-7833           Beaumont Bikes

Chainring         CR-7833           Bike Satellite Inc.

Chainring         CR-7833           Training Systems

Chainring Bolts   CB-2903           Beaumont Bikes

Chainring Bolts   CB-2903           Bike Satellite Inc.

Chainring Bolts   CB-2903           Training Systems

Chainring Nut     CN-6137           Beaumont Bikes

Chainring Nut     CN-6137           Bike Satellite Inc.

.....