Self Join

A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields.

      USE DatabaseName;
GO
SELECT DISTINCT tb1.ProductID, tb2.VendorID
FROM TableName tb1
INNER JOIN TableName  tb2
ON tb1.ProductID = tb2.ProductID
AND tb1.VendorID = tb2.VendorID
ORDER BY tb1.ProductID