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
46 Comments. Leave new
@Kiran
Post your query and post what error you see ?
IM.
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 0I 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
Nice and important article about self join,really its informative.
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.
@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.
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.
you are man of vision
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.
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
dear sir
your articles are really very use full for all users .
thanks a lot for create it.
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
Hi Pinal Dave
your blog is very useful. encourage all to alway visit this site for reference.
keep it up.
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()
nice article
nice nd very useful article.
Really fantastic Dave,
good article
A self join is an example of an inner join, the code example even says inner join. I would not classify this as a ‘type of join’ in its own right.