HomeОбразованиеRelated VideosMore From: kudvenkat

Part 2 SQL query to get organization hierarchy

574 ratings | 273564 views
Link for all dot net and sql server video tutorial playlists http://www.youtube.com/user/kudvenkat/playlists Link for slides, code samples and text version of the video http://csharp-video-tutorials.blogspot.com/2014/05/part-2-sql-query-to-get-organization.html To get the best out of this video, the following concepts need to be understood first. These are already discussed in SQL Server Tutorial. 1. Self-Join - http://www.youtube.com/watch?v=qnYSN_7qwgg 2. CTE - http://www.youtube.com/watch?v=ZXB5b-7HJHk 3. Recursive CTE - http://www.youtube.com/watch?v=GGoV0wTMCg0 Here is the problem definition: 1. Employees table contains the following columns a) EmployeeId, b) EmployeeName c) ManagerId 2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed. For example, Scenario 1: If we pass David's EmployeeId to the query, then it should display the organization hierarchy starting from David. Scenario 2: If we pass Lara's EmployeeId to the query, then it should display the organization hierarchy starting from Lara. We will be Employees table for this demo. SQL to create and populate Employees table with test data Create table Employees ( EmployeeID int primary key identity, EmployeeName nvarchar(50), ManagerID int foreign key references Employees(EmployeeID) ) GO Insert into Employees values ('John', NULL) Insert into Employees values ('Mark', NULL) Insert into Employees values ('Steve', NULL) Insert into Employees values ('Tom', NULL) Insert into Employees values ('Lara', NULL) Insert into Employees values ('Simon', NULL) Insert into Employees values ('David', NULL) Insert into Employees values ('Ben', NULL) Insert into Employees values ('Stacy', NULL) Insert into Employees values ('Sam', NULL) GO Update Employees Set ManagerID = 8 Where EmployeeName IN ('Mark', 'Steve', 'Lara') Update Employees Set ManagerID = 2 Where EmployeeName IN ('Stacy', 'Simon') Update Employees Set ManagerID = 3 Where EmployeeName IN ('Tom') Update Employees Set ManagerID = 5 Where EmployeeName IN ('John', 'Sam') Update Employees Set ManagerID = 4 Where EmployeeName IN ('David') GO Here is the SQL that does the job Declare @ID int ; Set @ID = 7; WITH EmployeeCTE AS ( Select EmployeeId, EmployeeName, ManagerID From Employees Where EmployeeId = @ID UNION ALL Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID From Employees JOIN EmployeeCTE ON Employees.EmployeeId = EmployeeCTE.ManagerID ) Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName From EmployeeCTE E1 LEFT Join EmployeeCTE E2 ON E1.ManagerID = E2.EmployeeId
Html code for embedding videos on your blog
Text Comments (46)
Harsh Vardhan Dewari (3 months ago)
Why is UNION ALL used, what will happen if UNION is used instead, I want to know the logic behing using Union ALL , thank you.
Gaayathiri (20 days ago)
UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will ignore the duplicate records whereas UNION ALL will include duplicate records. It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
Alok Das (5 months ago)
Hi, I faced the similar kind of question in an interview but the requirement was little different. If you pass the id as 7 which is David's id you should get the output of your last manager which is ben's record only. How we can do that?
VINIT upadhyay (6 months ago)
My favorite channel..
Matt York (7 months ago)
Hello..how to create a list of managers and their employees given the fact that each manager has more than one employee ? Thanks
Rakesh Ranjan (10 months ago)
Sir... You are great... Thank you...
ulysses1904 (11 months ago)
Great stuff but please stop saying "Superboss". I have never in my life heard anyone use that word and it gets repeated endlessly in these videos. How about "CEO" or "Owner" or "Department Head".
priyaranjan das (11 months ago)
God bless you I pray to my Lord full fill your every problem.
Bipin (1 year ago)
In case of row number... . For getting the nth highest salary.. We can use distinct instead of top 1... I think this will work..
Safiul Haque (1 year ago)
Great job thanks
dheeraj sharma (1 year ago)
The way you break the problem into pieces and then one by one solve them....its really great...really impressed by your work..... keep it up. god bless you :D
Amitanshu Mishra (1 year ago)
Query with out cte:- select e1.empName ,e2.empName as ManagerName from emp e1 Left join emp e2 on e1.ManagerId=e2.empid;
Rohan Sawant (1 year ago)
Amitanshu Mishra ohhh ok.. got you.. :-)
Amitanshu Mishra (1 year ago)
Rohan Sawant I am aware of that. but beginners will find it difficult to understand. so i posted the simple query. thanks again😇
Rohan Sawant (1 year ago)
Venkat's video gives us a hierarchy for a particular individual at a time. Your query won't do that.
Hiraji Jadhav (1 year ago)
Hi venkat sir recently I attended interview and there interviewer asked me SQL query as there are two tables item and item Rate item table consist of item id Item Name 1. Parker 2. Marie 3. Good Day 4. Monaco item Rate consist of ItemId ItemRate 1. 5 3. 20 and Query was. select I.iemName from item I left join ItemRate R on I.itemId!=R.itemId where R.itemRate>5 what is the output of above query please explain
Anirban Goswami (1 year ago)
Hi Hiraji Jadhav, I try to give the answer. It is an actually inner joins with a condition . Answer is "Marie, Good Day, Monaco" item names. Please correct me, if i am wrong. Regards,
Rajesh Gangineni (1 year ago)
Hiraji Jadhav Good day
Ali Hameed (1 year ago)
Hello Venkat, i am a big fan of you. You really are a great person indeed. I just pray may ALLAH the almighty shower HIS Countless blessings upon you. Aameen
Arvind kumar (1 year ago)
Hi Venkat, below query is not working in Declare @ID int ; Set @ID = 7; WITH EmployeeCTE AS ( Select e.EmployeeId, e.EmployeeName, e.ManagerID From Employees e Where EmployeeId = @ID UNION ALL Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID From Employees JOIN EmployeeCTE ON Employees.EmployeeId = EmployeeCTE.ManagerID ) Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName From EmployeeCTE E1 inner Join EmployeeCTE E2 ON E1.ManagerID = E2.EmployeeId In MSSQL 2012
Riyanka Bhowmick (1 year ago)
Really impressive...
Stone Wang (2 years ago)
best sql video in youtube. Thanks !
sabyasachi sahu (2 years ago)
Hi Venkat, One question I do have, if I want to join 10 tables then how many minimum "Join" statements I can use ?
Sanjeev Tech (2 years ago)
your every video is very easy to under stand and really very help full.
Vishal Tile (2 years ago)
"Except org hierarchy where we can use recursive CTE? " was one of my interview Q being asked to me. can you pl tell??
pakistani8907 (2 years ago)
Why is @ID set to 7?
Syed Mohd Naqi Zaidi (2 years ago)
very useful
Kamlesh Nakade (2 years ago)
Great Work Venkat. Really Impressed by the way of representation and examples,which are very clear to understand. Thanks a ton!!
kudvenkat (2 years ago)
+Kamlesh Nakade Thank you for the feedback. This means a lot. Very pleased you found these videos useful. Dot Net & SQL Server training videos to help you become a web developer https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd Download videos for offline viewing http://www.pragimtech.com/Order.aspx Slides, Code Samples & Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use our youtube channel https://www.youtube.com/watch?v=y780MwhY70s To receive alerts, when new videos are uploaded, please subscribe to our channel http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video May I request you to please share the link with your friends who you think would also benefit from them To teach is to learn Thanks Venkat
akshay mundhada (2 years ago)
Sir will that be possible for you to send us all the query u explain in 12 videos, that will be really helpful for me. my ID is [email protected]
Sumit Jangir (2 years ago)
Hello Sir, I have a doubt, This result can be get by following query select a.Employeename,isnull(b.employeename,'No boss') as manager from Employees a left join Employees b on a.ManagerID = b.EmployeeId then why we use CTE or recursive query?
Sumit Jangir (2 years ago)
Got it. Thanks Man :) 
vishwanath mali (3 years ago)
nice venkat sir it is really great job for needy people god bless u
TAHIR kamal (3 years ago)
We can do this with a simple Query, not necessairly a Recursive CTE, Can you show us a more concrete Example where only a CTE is possible, thank's :)
makda tesfa (3 years ago)
Thank you so much but what is ITT in SQL server.
Siddhu Kr (3 years ago)
I have 10,000 records of hierarchy data and if i go by this method it would have a serious impact on performance . So Sql has introduced a new datatype named hierarcyid which would give a better performance . Can you please post a video with above as example .
FlareSoul69 (3 years ago)
Loved this real world SQL application!! Please make more SQL videos like this!
Zaheer Shaikh (3 years ago)
Good tutorials... We can achieve the hierarchy of the organization with simple query also... Example here is select A.EMPLOYEEID,A.ENAME,A.DOB,A.GENDER,A.DESIGNATION, A.SALARY, Z.ENAME AS MANAGER FROM employees a inner join employees z on a.managerid = z.employeeid go select A.EMPLOYEEID,A.ENAME,A.DOB,A.GENDER,A.DESIGNATION, A.SALARY, ISNULL(Z.ENAME, 'NO BOSS') AS MANAGER FROM employees a left join employees z on a.managerid = z.employeeid
Kris Maly (4 years ago)
Good video
John D RIchardson II (4 years ago)
I upgraded to sql 12, now I get an error whener try to use the Keyword  with in the command  WITH RESULT AS in the CTE, 
Yashveer Gurjar (8 months ago)
use ;with Result as .You were missing a ';'
Saagar Soni (4 years ago)
thnx  a lot sir
Mahmoud Shouman (4 years ago)
Thanks For great job MR.kudvenkat , May you explain hierarchy id datatype and how to insert and retrieve Data by using  hierarchy   and XML Datatype
khadarbabu s (4 years ago)
sir, One Interview qustion i Faced ......find third highest number in arrays without using sorting..... and values will be give at run time...values must be distinct..no duplicate values... this is one of interview question 
Tan YEW MENG (4 years ago)
Sir, can you kindly explain how the second query after the UNION ALL within the EmployeeCTE works because I am finding the recursive CTE difficult to understand even after watching the Part 51 Recursive CTE
kudvenkat (4 years ago)
Sure, I think it would be easy to explain this in a video rather in text. Will try to record and upload a video in a few days. Thank you for your patience. Good Luck.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.