SQL SERVER – 2005 Explanation and Example – 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. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.

When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.

Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.

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

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL – Example courtesy

,
Previous Post
SQL SERVER – 2005 – Microsoft SQL Server Management Pack for Microsoft Operations Manager 2005 – Download SQL Server MOM 2005
Next Post
SQL SERVER – Database Coding Standards and Guidelines – Introduction

Related Posts

46 Comments. Leave new

  • AND pv1.VendorID pv2.VendorID

    Reply
  • Administrator :

    I am not able to place “not equal to ”

    between pv1.VendorID And pv2.VendorID

    Reply
  • Imran Mohammed
    May 15, 2009 7:58 am

    @Kiran

    Post your query and post what error you see ?

    IM.

    Reply
  • Clive Paterson
    May 28, 2009 3:03 pm

    Using less than in the join will eliminate the duplicate results:

    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.ProductID1

    Image the results set as a square grid where every row from pv1 is join with every row from pv2. Now, using a less than in the join will only join row from pv2 which have an ID greater than the ID in pv1, eliminating duplicates:

           pv2
    
         0 0 0 0 0 0
         x 0 0 0 0 0
    p   x x 0 0 0 0
    v   x x x 0 0 0
    1   x x x x 0 0
         x x x x x 0
    
    Reply
  • I have one observation regarding the example you gave:

    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

    You said it will “find products that are supplied by more than one vendor”. This query returns all products from the table, not just the ones supplied by 2 or more vendors

    Maybe you meant something more like this ( instead of =)

    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

    or even

    USE AdventureWorks;
    GO
    SELECT DISTINCT pv1.ProductID
    FROM Purchasing.ProductVendor pv1
    INNER JOIN Purchasing.ProductVendor pv2
    ON pv1.ProductID = pv2.ProductID
    AND pv1.VendorID pv2.VendorID
    ORDER BY pv1.ProductID

    Reply
  • Chandraakant H P
    December 4, 2009 12:21 pm

    Nice and important article about self join,really its informative.

    Reply
  • When we select multiple columns with Distinct as Clause
    it does not gives distinct values Please see below Eg.

    Employee is the Table with values

    Santosh Patil 19000
    Shilpa Shinde 18000
    Ajit 20000
    Pramod 15000
    Pravin 21000
    Prakash 15000
    Ajit 35000

    Query is

    select distinct ename,salary from employee

    O/P show is

    Ajit 20000
    Ajit 35000
    Prakash 15000
    Pramod 15000
    Pravin 21000
    Santosh Patil 19000
    Shilpa Shinde 18000

    Here there is Duplication of Name Ajit.

    Plz Help me in sorting out this Problem.

    Awaiting for Your Favourable reply.

    Regards.

    Reply
    • @Pravin

      DISTINCT works on the entire record, not just one COLUMN.

      Something likw: select ename, MAX(salary) from employee GROUP BY ename; might work for you.

      Reply
  • I’d like to make a qury to give me back the confliction beween starttime and endtime and the period between them,

    i want to insert a new field to Lecture table so that it must be not conflicted with the time of other lecture for the same teacher .

    thank for help.

    Reply
  • you are man of vision

    Reply
  • hi Pinal Dave,

    i have a doubt in self join,

    i have written query like this.

    SELECT DISTINCT R.NDC,R1.NDC,
    R.NPT_PRICEX as LastPrice,
    R1.NPT_PRICEX as NewPrice,
    from RNP2_NDC_PRICE R
    join
    RNP2_NDC_PRICE R1
    on R1.NDC=R.NDC
    and R1.IsActive=’TRUE’
    and R.IsActive=’FALSE’

    iam getting duplicate records through this query,because
    the table having more than one non active records with same NDC.

    please help me to avoid duplicate records.

    Reply
  • Result iam getting like this.

    00002323560 00002323560 189.00000 213.88000
    00002323560 00002323560 200.34000 213.88000
    00002323701 00002323701 374.39999 399.70001
    00002323704 00002323704 3533.33008 3997.00000
    00002323704 00002323704 3744.00000 3997.00000
    00002323730 00002323730 106.00000 119.91000
    00002323730 00002323730 112.32000 119.91000
    00002323733 00002323733 353.32999 399.70001
    00002323733 00002323733 374.39999 399.70001
    00002323734 00002323734 112.32000 119.91000

    Reply
  • some of you (Srinu, Pravin) are not reading all the comments. Clive Paterson has the solution about eliminating the duplicate records in his comment. So simple.

    as Clive said:

    “Using less than in the join will eliminate the duplicate results:”

    meaning join on the record1.ID < record2.ID as part of your join.

    clive's comment:
    https://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/#comment-52507

    Reply
  • dear sir
    your articles are really very use full for all users .
    thanks a lot for create it.

    Reply
  • i think u dont need self join to answer the query
    “find products that are supplied by more than one vendor”

    here is my solution

    SELECT ProductID, count(VendorID) as “Num OF Vendors”
    FROM Purchasing.ProductVendor pv1
    GROUP BY ProductID
    HAVING count(VendorID) > 1
    ORDER BY ProductID

    thank u in advance

    Reply
  • Hi Pinal Dave

    your blog is very useful. encourage all to alway visit this site for reference.
    keep it up.

    Reply
  • i want to display data on text box and few of label from two tables emp_master and formula besis of selection of emp_no in combo box, i write down follwing code..bt no data show on textbox and label. any body help …….plz…….

    Dim con As New SqlConnection(“Data Source=MICRODAT-39F82C;Initial Catalog=master;Integrated Security=SSPI”)

    Dim Da As New SqlDataAdapter
    Dim Da1 As New SqlDataAdapter
    Dim Ds As New DataSet
    Dim Ds1 As New DataSet
    Dim Dt As New DataTable
    Dim Dt1 As New DataTable
    con.Open()

    Try

    Da = New SqlDataAdapter(“Select f.Basic_sal,f.d_a,f.p_p,f.c_a,f.h_r_a,f.medical,f.o_a,f.o_t,f.p_f,f.o_p_f,f.loan,f.ad_vance,f.s_s_s,f.elec_charge,f.p_tax,f.i_tax,f.e_s_i,f.gross_sal,f.net_sal,f.date,e.emp_name,e.d_o_b,e.sex,e.d_o_j,e.gard_name,e.present_add from farmula f INNER JOIN emp_master e ON f.emp_no='” & ComboBox1.SelectedValue.ToString & “‘where e.emp_no=f.emp_no “, con)

    Da.Fill(Ds)
    Catch ex As Exception
    MessageBox.Show(ex.Message)

    TxtBx_EBsic.Text = Ds.Tables(0).Rows(0).Item(“f.Basic_sal”).ToString
    TxtBx_DA.Text = Ds.Tables(0).Rows(0).Item(“f.d_a”).ToString
    TxtBx_PP.Text = Ds.Tables(0).Rows(0).Item(“f.p_p”).ToString
    TxtBx_HRA.Text = Ds.Tables(0).Rows(0).Item(“f.h_r_a”).ToString
    TxtBx_Medi.Text = Ds.Tables(0).Rows(0).Item(“f.medical”).ToString
    TxtBx_CA.Text = Ds.Tables(0).Rows(0).Item(“f.c_a”).ToString
    TxtBx_OA.Text = Ds.Tables(0).Rows(0).Item(“f.o_a”).ToString
    TxtBx_OT.Text = Ds.Tables(0).Rows(0).Item(“f.o_t”).ToString
    TxtBx_Grssal.Text = Ds.Tables(0).Rows(0).Item(“f.gross_sal”).ToString
    TxtBx_pf.Text = Ds.Tables(0).Rows(0).Item(“f.p_f”).ToString
    TxtBx_Opf.Text = Ds.Tables(0).Rows(0).Item(“f.o_p_f”).ToString
    TxtBx_ptax.Text = Ds.Tables(0).Rows(0).Item(“f.p_tax”).ToString
    TxtBx_esi.Text = Ds.Tables(0).Rows(0).Item(“f.e_s_i”).ToString
    TxtBx_BLne.Text = Ds.Tables(0).Rows(0).Item(“f.loan”).ToString
    TxtBx_adv.Text = Ds.Tables(0).Rows(0).Item(“f.ad_vance”).ToString
    TxtBx_elec.Text = Ds.Tables(0).Rows(0).Item(“f.elec_charge”).ToString
    TxtBx_sss.Text = Ds.Tables(0).Rows(0).Item(“f.s_s_s”).ToString
    TxtBx_IT.Text = Ds.Tables(0).Rows(0).Item(“f.i_tax”).ToString
    TxtBx_netpay.Text = Ds.Tables(0).Rows(0).Item(“f.net_sal”).ToString
    txtbx_dt.Text = Ds.Tables(0).Rows(0).Item(“f.date”).ToString
    Label56.Text = Ds.Tables(0).Rows(0).Item(“e.emp_name”).ToString
    Label9.Text = Ds.Tables(0).Rows(0).Item(“e.sex”).ToString
    Label13.Text = Ds.Tables(0).Rows(0).Item(“e.d_o_b”).ToString
    Label57.Text = Ds.Tables(0).Rows(0).Item(“e.present_add”).ToString
    Label1.Text = Ds.Tables(0).Rows(0).Item(“e.gard_name”).ToString
    Label15.Text = Ds.Tables(0).Rows(0).Item(“e.d_o_j”).ToString

    End Try
    con.Close()

    Reply
  • nice article

    Reply
  • nice nd very useful article.

    Reply
  • Really fantastic Dave,

    Reply
  • good article

    Reply

Leave a Reply

Menu