Post reply

SELF JOIN

  • January 17, 2008 at 3:53 am

    #178363

    Please explain the use of self join with example

    Binu

  • January 17, 2008 at 4:10 am

    #769125

    A table can be joined to itself in a self-join. For example, you can use a self-join to find the products that are supplied by more than one vendor.

    Because this query involves a join of the ProductVendor table with itself, the ProductVendor table appears in two roles. To distinguish these roles, you must give the ProductVendor table two different aliases (pv1 and pv2) in the FROM clause. These aliases are used to qualify the column names in the rest of the query. This is an example of the self-join Transact-SQL statement:

    USE AdventureWorks;

    GO

    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

    -- SOURCE: BOL

  • January 17, 2008 at 5:49 am

    #769164

    Before asking the question do an article search on this site. I'd be incredibly surprised if there's not something on here about self joins.

    In fact, this article talks about most join scenarios http://qa.sqlservercentral.com/articles/Basic+Querying/2937/ 🙂

  • January 17, 2008 at 6:39 am

    #769185

    This also sounds suspiciously like you are asking us to do your homework for you.

  • January 18, 2008 at 12:28 am

    #769562

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply