tag:blogger.com,1999:blog-6082652835152798567.post4424089013638335110..comments2024-03-28T21:41:58.974-07:00Comments on Sql server, .net and c# video tutorial: Part 2 - SQL query to get organization hierarchyUnknownnoreply@blogger.comBlogger7125tag:blogger.com,1999:blog-6082652835152798567.post-45751268205172500562018-08-14T11:28:34.776-07:002018-08-14T11:28:34.776-07:00I want to go other way round.
Suppose I have been ...I want to go other way round.<br />Suppose I have been given a parent ID and i want to retrieve all the employees ID who are being manged by this manager Anonymoushttps://www.blogger.com/profile/06035434003008579848noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-11208554329589466592017-07-16T19:16:54.961-07:002017-07-16T19:16:54.961-07:00Declare @ID int ;
Set @ID = 4;
WITH EmployeeCTE AS...Declare @ID int ;<br />Set @ID = 4;<br />WITH EmployeeCTE AS<br />(<br />Select EmployeeId, EmployeeName, ManagerID, 1 as Level<br />From Employees<br />Where EmployeeId = @ID<br /><br />UNION ALL<br /><br />Select Employees.EmployeeId , Employees.EmployeeName,<br />Employees.ManagerID, EmployeeCTE.Level +1<br />From Employees<br />JOIN EmployeeCTE <br />ON Employees.EmployeeId = EmployeeCTE.ManagerID<br />)<br />Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName, <br />ROW_NUMBER () over (order by E1.Level desc) as Level<br />From EmployeeCTE E1<br />left Join EmployeeCTE E2<br />ON E1.ManagerID = E2.EmployeeId<br />order by Level descAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-61134994851926038652017-03-27T00:41:53.784-07:002017-03-27T00:41:53.784-07:00Can we use this in Asp.net MVC Application?Can we use this in Asp.net MVC Application?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-37453297313066235422016-04-03T01:11:03.373-07:002016-04-03T01:11:03.373-07:00Declare @ID int ;
Set @ID = 7;
WITH EmployeeCTE AS...Declare @ID int ;<br />Set @ID = 7;<br />WITH EmployeeCTE AS<br />(<br /> Select EmployeeId, EmployeeName, ManagerID<br /> From Employees<br /> Where EmployeeId = @ID<br /> <br /> UNION ALL<br /> <br /> Select Employees.EmployeeId , Employees.EmployeeName,<br /> Employees.ManagerID<br /> From Employees<br /> JOIN EmployeeCTE<br /> ON Employees.EmployeeId = EmployeeCTE.ManagerID<br />)<br />--Select * From Employees EmployeeCTE<br />Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName,ROW_NUMBER()over (order by E1.EmployeeId) as LEVEL<br />From EmployeeCTE E1<br />LEFT Join EmployeeCTE E2<br />ON E1.ManagerID = E2.EmployeeIdpankajpagarehttps://www.blogger.com/profile/11673951533859312266noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-3966401463302508142015-10-27T03:23:22.653-07:002015-10-27T03:23:22.653-07:00I am sorry i got i now that why did you use CTE ov...I am sorry i got i now that why did you use CTE over self join. my query is just selecting the the employee and his manager not the hierarchy!. <br />you done well.. <br />sorry again. <br />Anonymoushttps://www.blogger.com/profile/11130849273351701514noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-24771573888975912012015-10-27T03:15:38.320-07:002015-10-27T03:15:38.320-07:00Hello, Sir. Please clear me, why should i use CTE...Hello, Sir. Please clear me, why should i use CTE for this purpose? In that CTE we have 3 select 2 joins and 1 Union. <br /><br />I think i can be done using simple self join. <br /><br />SELECT <br /> Emp1.EmployeeName as EmployeeName, <br /> IsNull(emp2.employeename,'No Boss') as Manager <br /> From Employees Emp1 <br />Left Join Employees Emp2 on Emp.ManagerId = Emp2.EmployeeId<br /><br />And please let me know if i am doing somethign wrong?Anonymoushttps://www.blogger.com/profile/11130849273351701514noreply@blogger.comtag:blogger.com,1999:blog-6082652835152798567.post-22688720534508237182015-10-17T11:51:42.929-07:002015-10-17T11:51:42.929-07:00Can anyone suggest how to add a "Employee Lev...Can anyone suggest how to add a "Employee Level" column to this? For Example if hierarchy is John - reports to - Laura - reports to - Ben THEN the Levels will be 1 - 2 -3rainmakerhttps://www.blogger.com/profile/00571962934847885747noreply@blogger.com