What is self join and how to write query in sql server

Posted by Jitendrasoft09 under Sql Server on 6/26/2013 | Points: 10 | Views : 1110 | Status : [Member] [MVP] | Replies : 4
Hi All,
I want to understand What is self join and how to write query in sql server, let me know if anybody knows.

Jitendra Kumar
If my post helps you, plz mark as an answer.



Responses

Posted by: Satyapriyanayak on: 6/26/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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 have a hierarchal
reporting structure whereby one member of staff reports to another.

If this post helps you mark it as answer
Thanks

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Neeraaj.Sharma on: 6/27/2013 [Member] Starter | Points: 25

Up
0
Down

Always marks as Answer when you get the solution


Just to all with answer Self Join is not any kind join its just a situation when we join a same table itself(in some specific conditions).

Example below :
how you would get a Cumelative sum of a table ?



create Table CUMULATIVESUM (id tinyint , SomeValue tinyint )


**Now let put some data in the table**


insert into CUMULATIVESUM

Select 1 , 10 union
Select 2 , 2 union
Select 3 , 6 union
Select 4 , 10





Select c1.ID , C1.SomeValue , Sum(C2.Somevalue) CumlativeSumValue FRom CumlativeSum c1 , CumlativeSum c2
Where c1.id>=c2.ID
group by c1.ID , C1.SomeValue
order by c1.id asc



i hope now self join is clear to you



Visit my blog : www.tutorialsqlserver.com


Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 9/6/2013 [Member] Starter | Points: 25

Up
0
Down
Hi,

To Join within the Table is called self join.


select * from emp a inner join emp b on a.empid=b.emp where a.empid=101

hope this will help....




sriram

Jitendrasoft09, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Allemahesh on: 9/6/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
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.

Examples

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.

USE AdventureWorks2008R2;

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


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, 

sp.SalesQuota, sp.SalesYTD
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesTerritory AS st
ON sp.TerritoryID = st.TerritoryID
ORDER BY st.Name, sp.BusinessEntityID


Happy Coding.

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

Login to post response