How to write a self join query

Posted by Rrana under Sql Server on 12/14/2011 | Points: 10 | Views : 3652 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi All,

i need to write a self join query

My Table:-

id locid parentid filename

1 1 0 word
2 1 0 excel
3 1 1 power
4 2 0 word
5 2 4 power

I need to retrive only data where parentid=0 using selfjoin

Thanks in Advance

Sucharitha

Sucharitha Goud
Bank Of America..

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Sksingh on: 12/14/2011 [Member] Starter | Points: 25

0	Hi, From your query its not clear based on which do u want to use self join. Anyway below is one example which will give u clear picture about self join. Suppose Employees is table which contains data related to employees. An Employee can be manager for other employee but single itself consider as employee for any organization. Exmaple: TableName : employees EmployeeID EmployeeName ManagerID 1 A null 2 B 1 3 C 1 4 D 3 5 E 3 select e1.EmployeeName "Employee", e2.EmployeeName "Reports To" from employees e1 left outer join employees e2 on e1.ManagerID = e2.EmployeeID order by e1.EmployeeID OutPut will be as below: Employee Reports To -------------------------------------------------- -------------------------------------------------- B A C A D C E C Regards, Sunil Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

From your query its not clear based on which do u want to use self join.

Anyway below is one example which will give u clear picture about self join.

Suppose Employees is table which contains data related to employees.
An Employee can be manager for other employee but single itself consider as employee for any organization.

Exmaple:



TableName : employees 



EmployeeID 	EmployeeName 	ManagerID 

1 		A 		null 

2 		B 		1 

3 		C 		1 

4 		D 		3 

5 		E 		3 



select e1.EmployeeName "Employee",

               e2.EmployeeName  "Reports To"

          from employees e1 left outer join employees e2

            on e1.ManagerID  = e2.EmployeeID 

         order by e1.EmployeeID 



OutPut will be as below:



Employee                                           Reports To

-------------------------------------------------- --------------------------------------------------

B						   A

C						   A

D						   C

E						   C

Regards,
Sunil

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

Posted by: Cnagasa on: 12/14/2011 [Member] Starter | Points: 25

0	Hi Sucharitha , Execute below the query u get the result select a.filename 'file name',b.filename 'file parent name' from MyTable a join MyTable b on a.parentid = b.id WHERE b.parentid =0 Regard's Sudarshan.C sudarshan.c Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriramnandha on: 5/20/2012 [Member] Starter | Points: 25

0	SELF JOIN: SELECT A.EMPID, B,EMPNAME ,A.SALARY FROM EMPLOYEE A, JOIN EMPLOYEE B ON A.EMPID=B.EMPID REGARDS sriram Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Ajay.Kalol on: 5/21/2012 [Member] Starter | Points: 25

0	http://ajaypatelfromsanthal.blogspot.in/2012/05/self-join-in-sql-server.html Here is simple solution... Ajay ajaypatelfromsanthal.blogspot.in Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Jporter892 on: 9/15/2013 [Member] Starter | Points: 25

0	The key with writing a self join query is to keep in mind the fact that the table will need to be referred to as two different entities (even though it's the same table) and the way this is achieved is through the use of aliases. In other words, because there is only one table which is being joined you essentially need to give the table two different names in the self join itself so that you are almost pretending that it's two different tables. See more details here, great page: http://www.programmerinterview.com/index.php/database-sql/what-is-a-self-join/ Rrana, if this helps please login to Mark As Answer. \| Alert Moderator

The key with writing a self join query is to keep in mind the fact that the table will need to be referred to as two different entities (even though it's the same table) and the way this is achieved is through the use of aliases. In other words, because there is only one table which is being joined you essentially need to give the table two different names in the self join itself so that you are almost pretending that it's two different tables. See more details here, great page:

http://www.programmerinterview.com/index.php/database-sql/what-is-a-self-join/

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

Latest Posts