Dear Jitendra Kumar,
A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name. This table alias helps the query processor determine whether columns should present data from the right or left version of the table.
A. Using a self-join to find the products supplied by multiple vendors
The following example uses a self-join to find the products that are supplied by more than one vendor.
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID <> pv2.VendorID
ORDER BY pv1.ProductID
B. Using a self-join to match sales people and their territories
The following example performs a self-join of the Sales.SalesPerson table to produce a list of all the territories and the sales people working in them.
SELECT st.Name AS TerritoryName, sp.BusinessEntityID,
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID
If it helps you or directs U towards the solution, MARK IT AS ANSWER Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator