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 (http://blog.SQLAuthority.com) , BOL – Example courtesy
Gud Arcticle.
liked it. Its a good article
Nice Article
really gud
I really liked this article.
really i like ur response
me 2.. :)
very nice article
didnt understand please give an easier example i am just learning SQL please forward me 1 if possible all help will be appriciated
i want to know detail info about cursor and trigger can u help me .
With your example, I have the following problem:
List pairs of vendor names who have the same ProductID. But if I use Self Join like the below script, the result will be redundant (2 pairs are the same except their orders). What should I do to get the exact pairs I need? Thank you.
USE AdventureWorks;
GO
SELECT DISTINCT pv1.VendorName, pv2.VendorName
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID != pv2.VendorID
(The result will be:
Vendor1 Vendor2
Vendor2 Vendor1
Vendor3 Vendor4
Vendor4 Vendor3
…
)
Keep it Up
nice article on self join
Nice Example
Nice Example
very nice article for self join……………
Gud article for selef join..
I think that the example needs to be corrected in the AND part of the code. ProductID needs to be the same, but the VendorID must be different to get products supplied by multiple vendors!
AND pv1.VendorID pv2.VendorID
[...] This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read More Here) [...]
I think above example needs to be modified to get the correct result,
i have used cte(common table expression) for that:
with vendorcount(productidcount) as
(
select productid from purchasing.productvendor
group by productid having count(vendorid)>1
)
select productidcount,vendorid from vendorcount inner join Purchasing.ProductVendor
on productidcount=productid
any suggestions would be appreciated
You are one man with great words.
Excellent!!!
I think the query should be modified as below:
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
it works efficiently
AND pv1.VendorID pv2.VendorID
Administrator :
I am not able to place “not equal to ”
between pv1.VendorID And pv2.VendorID
@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
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:
http://blog.sqlauthority.com/2007/06/03/sql-server-2005-explanation-and-example-self-join/#comment-52507
[...] be the outer join, I often get a request for an example for the same. I have created example using AdventureWorks Database of Self Join earlier, but that was meant for inner join as well. Let us create a new example today, where we [...]
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.
[...] This is a particular case when one table joins to itself with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read more here) [...]
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.
That is the point. Self join can be outer, inner or cross join and it is not of type of its own.