Recursively Querying Related Rows with SQL

Check out the related blog post for all code samples:
bertwagner.com/2020/08/25/rec...
Another one of my videos about how recursive queries work: • SQL Server Multiple Re...
Subscribe and turn on notifications to never miss a video: ruclips.net/user/DataWithBer...
Follow me on Twitter:
/ bertwagner

[@zanonilabuschagne7628]

Hey Bert. Good vid!!!
Ahhhh, Ragged Hierarchies - the gift that keeps on giving!!!!
This is my favourite interview question. Then, for bonus round, you add a self-joining record, e.g. add to your dataset "23,'R','R'", and see the poor candidate's code hit the wall... (Real Scenario in HR - a manager starts a division and has to work in it. So he is his own boss. It really does happen!! )Then you can see if a candidate has truly worked with hierarchies!!
This is one optimisation Microsoft did get right. I don't know if you ever had to build an HR or MEdical DW with ragged hierarchies before CTE's but it was terrible!!!
Thanks for this vid!!! Another great one!! Cheers

[@anthonylewis978]

Very cool, Bert. Thanks for the great ideas. I'm sure I'll be using these concepts soon...something always comes up!

[@CenterpointConnect]

This is awesome, great tip Bert! Thanks as always.

[@abrahamheredia4854]

Hi Bert, Very Good example ... A tricky one , its nice to see you often .. Keep With the good work.

[@ismaellopez9438]

You are the BEST Bert!!! Thank you

[@Kar08Mie]

Awesome channel Bert. Subscribed!
Can you please let me know what green screen and lighting you use? Thanks!

[@fafza1]

Great tutorial. I have about 7 columns that I can match on instead of just two. Any tips on how to approach that?

[@sb-qr4gb]

Thanks for this video. It is very helpful. Can you do a video about CTE?

[@ashishbhatia46]

Great explanation. Thank you for making cool video.

[@aviad-co1]

Hi, great stuff... I have a question about if i have two queries run with diffrent parameters... Both of them run with the same execution plan and doing the same logical reads.. but i still get different times running... How can i deal with that ? Thanks

[@George-qw8mp]

Hello Bert, i just find one of your video and immediately like your way how you speak and express things. This last video is 8 months old. Are you planning to make new videos?

[@Euclidean3141]

I’m trying to relate this example to a problem I had. How does this differ from a BOM (bill of materials) relationship? Aren’t the ‘groups’ 1, 2, 3 etc... effectively a higher level of the hierarchy? If so you don’t appear to have taken in to account bad data that might not be obvious in large data-sets. For example someone might link a lower level item back to the top and cause an infinite loop. I had a go at adding this ‘limit’ to the recursion by in my work by:
(1) adding a column to the anchor select which was “1 AS [Level]” and then
(2) adding a column to the second select part which was “[Level]+1”
(3) adding the following where clause to the second select “WHERE [Level] < 10” which would limit the recursion to 10 iterations.

[@loucaviotto4193]

Wow, so what job would require recursive querying?

[@KayWessel]

Thanks Bert, have you stopped producing video's completely?

[@fj8551]

I have the same question, i hope nothing bad happened to him

[@jovansomborski9795]

YOU ARE GOD! Thank you!