How to write a self join query

Posted by Rrana under Sql Server on 12/14/2011 | Points: 10 | Views : 2527 | Status : [Member] | Replies : 5
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..



Responses

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

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

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

Up
0
Down
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
Posted by: Jporter892 on: 9/15/2013 [Member] Starter | Points: 25

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

Login to post response