my selfjoin query getting error

Anjali Khan

8y816

Hi frnds

Below my table

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

EmpId EmpName ManagerId

______________________________________

1 A 1

2 B 1

3 C 2

4 D 3

5 E 4

and my self join query is

select e1.EmpName as ManagerName, e2.EmpName as EmpName

from EmpTest

selfjoin EmpTest

On e1.EmpId = e.ManagerId

when i execute the query it is showing error like

Msg 102, Level 15, State 1, Line 6

Incorrect syntax near 'EmpTest'.

please provide the write ans

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dilip Gupta8y ago
Accepted answer
Hi Anjali,

select e1.EmpName as ManagerName, e2.EmpName as EmpName
from EmpTest
selfjoin EmpTest
On e1.EmpId = e.ManagerId

In above query you have used selfjoin keword. joining a table with itself is called as SELF JOIN. SELF JOIN is not a different type of JOIN. It can be classified under any type of JOIN - INNER, OUTER or CROSS Joins.

Please refer the following link:
http://csharp-video-tutorials.blogspot.in/2012/08/self-join-in-sql-server-part-14.html
0
Nitin Sontakke8y ago
What did i say in my original reply then?
0
Anjali Khan8y ago
frnds

thanks for the update ,below queries are working if i keep left outer or simple join,

but if i want to use selfjoin getting error , how to do this using selfjoin becz got the qus in an interview ,use this only using self join

select e1.EmpName as ManagerName, e.EmpName as EmpName
from EmpTest e
left outer join EmpTest e1 on e1.[EmpId] = e.[ManagerId]

select e1.EmpName as ManagerName, e.EmpName as EmpName
from EmpTest e
join EmpTest e1 on e1.[EmpId] = e.[ManagerId]
0
Nitin Sontakke8y ago
Also, for empId 1 the managerId should be NULL.
0
Nitin Sontakke8y ago
Why not at least google for the syntax. There is no such thing as 'self join' in SQL. When a table is joined to itself it is called 'self join' by us humans. SQL is obviously not going to care for it.
select e1.EmpName as ManagerName, e.EmpName as EmpName
from EmpTest e
left outer join EmpTest e1 on e1.[EmpId] = e.[ManagerId]
Hope it helps!
+2
Dilip Gupta8y ago
Just use join keyword in above query
0