Recursive CTE in sql server Part 51 Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

hello welcome to presume technologies I
am venket this is part 51 of signal
server in the session learn about
recursive common table expressions and
the problems they solve before
continuing with the session I strongly
recommend to watch parts 49 and 50 of
this video series I have this employee
table which has got employee ID name and
manager ID columns since a manager is
also an employee in that organization
you know we store both employee and
manager details in the same table now if
I ask you who is the manager of mic you
will look up the manager ID column which
is number two number two you will then
take that value and look up in the
employee ID column number two is Josh so
Mike's manager discharge so we are
taking the manager ID column and looking
up another column in the same table so
you are referencing the same table so
it's called as a self referencing table
now if I ask you to display employee
name along with their manager name then
we have to join this table with itself
and we know that joining a table with
itself is called as self join and we
have spoken about self joins extensively
in part 14 of this video series okay so
this is simple self joined query so
obviously if I just want to display the
employee names along with the manager
names we can make use of a single self
join okay and if you look at this Josh
manager ID is now okay which means Josh
does not have any manager so it
basically displays null here but we are
displaying super boss with the help of
is null function so if manager dot name
is null then we are replacing that null
with super boss okay so and we have
spoken about different ways of replacing
null values in part 15 of this video
series okay so the reason why we talked
about this example here is that we are
going to extend this a little further
and then we will see how to solve it
using a recursive CT so if you just want
to display employee name along with
their manager name
you can simply use a self join and that
would solve you know what we are
expecting as far as the output is
concerned but let's say I want to
display the employee name their manager
name and along with that I also want to
display the level in the organization
okay now if you look at this
organization chart here Josh is the
super manager because he doesn't have a
manager ID which means he's at the top
of the hierarchy so and then to Josh
there are two direct reports Tom and
Mike you know are the direct reports of
Josh how can we tell that if you look at
Josh his employee ID is number two and
Tom and Mike their manager ID is number
two so obviously Tom and Mike report to
Josh and then Tom in a similar fashion
has got three direct reports Mike has
got two of them and Sam pam has got one
direct report so if you look at the
hierarchy Josh is that at the highest
level and Sam is at the bottom of the
hierarchy and so if you look at the
levels of these employees Josh level is
one he's at the top so that's why I
level one and if you look at Tom and
Mike they are level two so Tom and Mike
you can see employees Tom and Mike the
level is two along the same lines Pam
James Simon John and Mary level three
and at the bottom of the hierarchy is
Sam who is level four so right now I
want to display employee name their
manager name and their level within the
organization now to achieve this we can
use recursive cities using recursive
cities the code will become a lot easier
okay let us see how to solve this using
a recursive CTE so this is the recursive
city I mean we have spoken about you
know how to create a city itself in the
previous session so we are not going to
talk about the basics of CDE here we
will see how to create a recursive city
well what is a recursive city a CTE that
references itself is called as a
recursive city now in simple terms
forget about everything that you see on
this
we know that we create a CTE using with
keywords so with employee CTE so here
employees CTE is a CTE and then this is
obviously having four columns employee
ID name manager ID and level columns the
reason why I have put level in square
brackets is because level is a keyword
in sequel server if you if you want to
use a keyword as a column name then you
will have to wrap that inside the square
brackets okay so here basically employee
CTE is a CTE and if you look at the CTU
query within the ass block here we are
again referencing the CTE within the
city we are referencing the city so this
kind of becomes a self referencing CTE
okay
now let's understand what we are trying
to do now if you look at the output what
we are expecting we want the employee
name manager named level okay but then
the way we are implementing here I
should look at the first look at this
CTE itself so employee CD which has got
four columns as and look at this this
first query is pretty simple to
understand we are selecting you know
obviously our CD is written in full
columns employee ID name manager ID and
level so our select query should also
return four columns employee ID name
manager ID and one as level be
hard-coded that one and we'll I'll tell
you why in a short while
so select these four columns from TBL
employee where manager ID is now so if
you look at this query from this table
TBL employee we are selecting employee
ID name manager ID and then a hard-coded
value of one from TBL employee where
manager ID is now so for which record is
the manager ID now for Josh record
manager Idina is now so we only get Josh
record and you know Josh is the super
boss in the organization and he should
get a level of one okay so we are
selecting the level of one for Josh
that's hard-coded here and we only get
Josh record because we are checking
whether if it is null and who has got
null
only Josh so we get only Josh record and
his level will be one okay so that's the
first query which is very simple to
understand and then we are using Union
all operator now when you use a Union
all operator the number and type of
columns that are returned in the first
select query should match you know to
the number and type of columns returned
from the second query as well
so if you look at this one in the Select
query so if you look at from the out
purposeful perspective we just finished
retrieving this wrong now we have to
retrieve the rest of the rows and
increment their levels as they move down
in the hierarchy we should increase
their levels okay so for Tom and Mike
the level should be two and for the rest
of the you know this five people it
should be three and for parents it
should be four and to do that what we
are basically doing is we are selecting
the you know look at this look at the
join query first we are joining employee
CTE with TBL employee okay so we are
joining the CTE with TBL employee and in
TBL employee we have got all these rows
okay so what's going to happen now we
are going to recursively loop through
TBL employee and look at this from TBL
employee from the stable TBL employee we
are selecting three columns employee ID
name and manager ID but the fourth
column look at what we are doing we are
taking that from employees CTE dot so if
you look at this this the employee CTE
and it's got a level column so employees
CT dot level +1 to the existing level
just add one more okay so we are
referencing that level column from the
CTE itself and then we are getting
employee ID name and manager ID from TBL
employee okay so we require these four
columns from this query as well they are
getting those by joining TBL employee
with employees CTE and look at
this on Claus is another interesting one
so you are looking up the manager ID
column in the TBL employee table and you
are matching that to employee CTE dot
employee ID okay so that's the city
itself and you're doing a union all so
this first select query will give the
manager I mean super boss details and
then this recursively will retrieve all
the other members incrementing their
levels okay and then so this is just the
city
okay so if you look at the output I mean
if you look at the columns employee ID
name manager ID and level okay so the
CDE will have all of them so let's see
just that part now so if you look at the
CD itself so until now we have discussed
this okay so let's say let's comment
this section for a bit and let's say
select star from this employee CDE
so whatever CD we have constructed we
are selecting all the rows and columns
from that CDE so let's execute that and
look at this I get this output employee
ID name manager ID and level so we are
getting the level as expected now all we
have to do is instead of you know
displaying manager ID and employee they
just one name and a manager name okay to
do that we can simply use a self join
okay so we can join the CDE with itself
now and that's pretty simple just like
how you join a table with itself you can
join join a CTE with itself because a
CTE is also a temporary result set so
for that purpose what we are doing here
so let's uncomment this so if you look
at this we want the employee name and
manager them so if you look at this
employee CTE we are giving it to aliases
one is called as EMP CTE the other one
is called as manager City
we are giving those aliases and from
employee C T you are selecting the name
and that's nothing but employee name and
then you are checking from the manager
CTE you are selecting the name and
you're checking because a manager name
can be null for the super boss so that's
why instead of displaying null we are
replacing that with super boss and we
are giving this column an alias called
manager from TBL employee and obviously
we require the level as well and you can
get the level either from employee C T
or manager C T because it's the same
that's it so this part is pretty simple
all we have to understand here is the
second part which is a little confusing
you're joining TBL employee with
employee CDE and then you are
incrementing the level that's the piece
that we need to understand so obviously
now if we execute this we should get the
data that we are expecting so employee
name manager name and their level on
this slide you can find resources for
asp.net c-sharp and sequel server
interview questions if you want to
receive email alerts when I upload new
videos please subscribe to my channel
that's it for today thank you for
listening have a great day