my selfjoin query getting error

Question

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

Dilip Gupta · 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

Nitin Sontakke · Answer

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!

Nitin Sontakke · Answer

What did i say in my original reply then?

Anjali Khan · Answer

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]

Nitin Sontakke · Answer

Also, for empId 1 the managerId should be NULL.

Dilip Gupta · Answer

Just use join keyword in above query