Part 2 SQL query to get organization hierarchy

[@Ds_Unangi]

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

[@Adinasa2]

One of the most complex queries i have ever seen! great work!

[@zaheershaikh0214]

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

[@stuti.sharma.ranchi]

This is a great case! I went through self-join, CTE, and recursive CTE videos, and last but not least this interview case. Very comprehensive understanding of these concepts now. Thank you!

[@ukkashs444]

Where are you working frd??

[@raghuuvamsii]

Excellent sir, I'm a big of fan of your way of teaching. Getting the knowledge effectively is good thing but sharing that is great thing.

[@alihameed2457]

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

[@kamleshnakade2277]

Great Work Venkat. Really Impressed by the way of representation and examples,which are very clear to understand. Thanks a ton!!

[@Csharp-video-tutorialsBlogspot]

+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 kzbin.infoplaylists?view=1&sort=dd Download videos for offline viewing www.pragimtech.com/Order.aspx Slides, Code Samples & Text Version of the videos can be found on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our youtube channel kzbin.info/www/bejne/r2ibYYCtnb5qZtU To receive alerts, when new videos are uploaded, please subscribe to our channel kzbin.info 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

[@frankrobert9199]

Best sql tutorial I have seen.

[@anilkumarreddy2769]

impeccable explanation Mr.Venkat

[@vishwanathmali9008]

nice venkat sir it is really great job for needy people god bless u

[@davidramirez3322]

Thnks Venkat, Can't stop watchig your work and great tutorials.

[@FlareSoul69]

Loved this real world SQL application!! Please make more SQL videos like this!

[@SanjeevVermasanjeevr0728]

your every video is very easy to under stand and really very help full.

[@priyaranjandas762]

God bless you I pray to my Lord full fill your every problem.

[@bipin1042]

In case of row number... . For getting the nth highest salary.. We can use distinct instead of top 1... I think this will work..

[@siddhukr2643]

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 .

[@fired_developer]

Thanks for the info

[@khadarbabus9962]

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 

[@rickymartin8759]

best sql video in youtube. Thanks !

[@tahirkamal1878]

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 :)

[@kids_world_2k]

You have posted this question 9 yrs before but i attended interview on 29 June 2023 where this question is asked. But i saw it now the next day..other wise i would have answered 😢

[@Aman-x5g]

Yes

[@bluesage1528]

Great! Thank you👌

[@fernandojackson7207]

Nice. You can do this with Hierarchyid too.

[@nanani1729]

Hi @kudvenkat, If there is Union All, for every level up, David Record has to be appended once correct? There must be some duplicates atleast? But your output doesn't have any duplicates. Can you let me if I'm thinking correct, will there be any duplicates at all?

[@mmmarketing5683]

my question here is how do u know were to add , * ( ) = @ . "" " is their a rule that i should know? could u help me pls

[@hirajijadhav3135]

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

[@rajeshgangineni6333]

Hiraji Jadhav Good day

[@ganirban82]

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,

[@VinodKashyap-jb4ny]

u wil get good day

[@deepakvashishtha9669]

The output will be:- Parker Marie Monaco Answer Explanation:- These two tables has left join and not equal condition so the below query gives the 6 results on not equal condition select I.itemName from item I left join ItemRate R on I.itemId!=R.itemId output:- ItemName ICode RCode Rate Parker 1 3 20 Marie 2 1 5 Marie 2 3 20 Good Day 3 1 5 Monaco 4 1 5 Monaco 4 3 20 And in the last query has where clause rate>5 so the final query output will be Parker Marie Monaco Tx, -Deepak Vashishtha

[@sunmeetsingh8136]

Awesome video sir. Where can we get the scripts for the tables that you use?

[@RakeshRanjanSingh-23]

Sir... You are great... Thank you...

[@neemkarolimaharajji-j6t]

sir aapne set and declare k baad 0id likha h ya kuch or? kuch smjh nhi aara?

[@sumitjangir48]

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?

[@sumitjangir48]

Got it. Thanks Man :)

[@alwaysbehappy1337]

I got an error :- The statement terminated. The maximum recursion 100 has been exhausted before statement completion. Please Explain it.

[@tk200895]

I am also getting the same error

[@Boomstck]

Sounds like you created an infinite loop, unless if the query you are running is different from this tutorial. If this is a different query, then you can solve this issue by using OPTION (MAXRECURSION 0)

[@travelling_owl]

Query with out cte:- select e1.empName ,e2.empName as ManagerName from emp e1 Left join emp e2 on e1.ManagerId=e2.empid;

[@kehtahaipalpal]

Venkat's video gives us a hierarchy for a particular individual at a time. Your query won't do that.

[@travelling_owl]

Rohan Sawant I am aware of that. but beginners will find it difficult to understand. so i posted the simple query. thanks again😇

[@kehtahaipalpal]

Amitanshu Mishra ohhh ok.. got you.. :-)

[@ymtan]

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

[@Csharp-video-tutorialsBlogspot]

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.

[@Arvindkumar-wu3gj]

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

[@anbumani6083]

OK i got it. When the query before union runs already the CTE has one row. Then after union query, it starts processing from that available first row. Am i correct? Thanks.

[@SuperJV4x]

I need to do this from top down, not bottom up as in this example (i.e. give the manager and get all the subtrees of employees)

[@androidsavior]

Same here, Did you do it ?

[@rajendrakushwaha7185]

As a fresher for interview is it important to know about sql server . As i have college placement for sql developer? how to preprare for interview and which portion

[@BuyLAHouse888]

Thank you sooooooo much for great resourse made.

[@leonardchamin]

such a great video. Thank you

[@sabyasachisahu7663]

Hi Venkat, One question I do have, if I want to join 10 tables then how many minimum "Join" statements I can use ?

[@riyankabhowmick697]

Really impressive...

[@prattipatinaveena6187]

Sir I have one doubt.if I am passing anyone id in the company I want only ceo name

[@mattyork5143]

Hello..how to create a list of managers and their employees given the fact that each manager has more than one employee ? Thanks

[@vishalbtile]

"Except org hierarchy where we can use recursive CTE? " was one of my interview Q being asked to me. can you pl tell??

[@shaiksajid1096]

मराठी वादळ - छत्रपती शिवाजी महाराजI'm

[@alokdas6628]

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?

[@elishadeshawn2599]

pro tip: watch movies at Flixzone. I've been using it for watching lots of of movies lately.

[@brodiegianni235]

@Elisha Deshawn yea, have been watching on flixzone for since december myself :D

[@nikhilkumarchaudhary9593]

i am facing error of---> Must declare the scalar variable "@ID".

[@harshvardhandewari8147]

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.

[@gaayathiri5514]

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.

[@makdatesfa4337]

Thank you so much but what is ITT in SQL server.

[@mario17-t34]

Thanks much V!!

[@kvclips7]

Input Col1 A B C D Output Col1 Col2 A. B B. C C. D D. A Can you please help me to find out the script for the above requirements

[@pastorey2]

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,

[@Spiritualsoul25]

use ;with Result as .You were missing a ';'

[@raghuuvamsii]

@kudvenkat Sir, why don't you provide carrer advice us as a successful person. I mean how to make plan to become successful developer in .net from fresher level.

[@dipintigupta9777]

can be possible Sql Query convert in hierarchy

[@pakistani8907]

Why is @ID set to 7?

[@nikhilkumarchaudhary9593]

can anyone help what should be the query, if desired output ll be in a single column like----> David Tom Steve Ben

[@akfsf00543]

SELECT EMPLOYEEID, EMPLOYEENAME, (SELECT EMPLOYEENAME FROM EMPLOYEES WHERE EMPLOYEEID = A.MANAGERID) AS MANAGER from Employees AS A;

[@kleberoliveira5618]

If I have two super Boss???

[@safiulhaque3736]

Great job thanks

[@gkmishra2009]

Give video on how consume web api in wcf

[@syedmohdnaqizaidi5924]

very useful

[@devarakondasankararao3802]

I like this video much More

[@krismaly6300]

Good video

[@leelay4749]

I was asked below question today in #Microsoft technical interview. There are 5 micro services writing telemetry to Log Table Log schema includes the following. a. SourceID b. Message Description c. Severity d. Timestamp. Logs written to Log Table are asynchronous. configure an alert and notification to be sent when more than 5 severity 3 logs are written in a 5 mins window. Basically the question is, result set should contain a alert record of a particular sourceID whose severity is 3 and occurred more than 5 times and also the occurances with in 5 min window. Can anyone help me with the approach and query for this question?

[@ulysses1904]

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".

[@vinitsawant8104]

For MySQL with recursive cte (EmployeeID, EmployeeName, ManagerID) as ( select EmployeeID, EmployeeName, ManagerID from employeestemp where EmployeeID = 5 union all select p.EmployeeID, p.EmployeeName, p.ManagerID from employeestemp p inner join cte on p.ManagerID = cte.EmployeeID ) Select E1.EmployeeName, IFNULL(E2.EmployeeName, 'No Boss') as ManagerName From cte E1 LEFT Join cte E2 ON E1.ManagerID = E2.EmployeeId;