Multiple student can go to 1 teacher, but 1 student can't go to multiple teacher. There is no limit of level in this teacher student hierarchy.

How using a single SQL query in SQL Server we can get the following for any student
a) top teacher
b) all student teacher linked to that student

Multiple student can go to 1 teacher, but 1 student can't go to multiple teacher. There is no limit of level in this teacher student hierarchy.

How using a single SQL query in SQL Server we can get the following for any student
a) top teacher
b) all student teacher linked to that student

• sql
• sql-server
• hierarchy
• hierarchical-data

• 2 As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Commented Mar 5 at 21:36
• 1 What have you tried? Where did you get stuck? – Dale K Commented Mar 5 at 21:37
• For student 500, why does 400 appear in their list? – MatBailie Commented Mar 6 at 22:25

2 Answers 2

As your "linked student teacher Ids" is the same for every student under a given head teacher, you'll probably want to compute that list once per head teacher (instead of computing it as many times as you have students).

Then a Common Table Expression will allow you to:

• write query parts incrementally
• improve readability
• … and use recursivity

From the point of view of the database, this is only one query (think "subqueries written sequentially").

with
  -- h (Hierarchy) is computed recursively. Each step gets its level (which will help order the groups later)
  h as
  (
    -- Head teachers are those who are not student either.
    select distinct TeacherId as id, TeacherId as HeadTeacherId, 0 hlevel from t where not exists (select 1 from t UberTeacher where t.TeacherId = UberTeacher.StudentId)
    union all
    select StudentId, HeadTeacherId, hlevel + 1
    from h join t on t.TeacherId = h.id
  ),
  -- Now compute the whole group only once per head teacher.
  heads as
  (
    select HeadTeacherId id, string_agg(id, ',') within group (order by hlevel desc, id desc) WholeGroup
    from h
    group by HeadTeacherId
  )
-- Finally each student gets a copy of its head teacher's list.
select h.id, HeadTeacherId, WholeGroup
from h join heads on h.HeadTeacherId = heads.id;

This gives the exact result you are looking for.

New version

Find all the nodes of the tree (hierarchy) that are connected to a certain specified node.

Query parts:

1. CTE r1 'up' thru hierarchy. Anchor part static row, where linkId=@searchId
2. CTE r2 'down' thru hierarchy. Anchor part - output from previous CTE.
3. Aggregation with distinct linkId's

declare @searchId int=100;

with r1 as(
  select 1 lvl,cast('base' as varchar(4)) dir,@searchId root ,@searchId linkId  
  union all
  select  lvl+1 lvl,cast('up' as varchar(4)) dir,r.root,t.teacherId as linkId
  from r1 r inner join hierarchy t on r.linkId=t.studentId
)
,r2 as(
  select lvl lvl, dir,root,linkId ,max(lvl)over() maxLvl
  from r1
  union all
  select  lvl+1 lvl,cast('down' as varchar(4)) dir,r.root,t.studentId as linkId ,0 maxLvl
  from r2 r inner join hierarchy t on t.teacherId=r.linkId
)
select root
  ,max(case when (dir='up' or dir='base') and lvl=maxlvl then linkId end) as high_teacher 
  ,string_agg(linkId,',')within group(order by linkId) as links
from (
  select  *,rank()over(partition by linkId order by lvl desc)rnk 
  from r2
  )t
where rnk=1
group by root
;

Demo

Old version

Find all parents and childs for certain specified node.

Consider case when you want take data for one student from hierarchy.
We define this as declare @searchId int=50;

To get all the child and parent nodes of the tree, you need to go recursively up and recursively down the tree (hierarchy).
For simplicity, I'm using two recursive queries.
Also, this approach may have better performance on a large table.

r1. Search down thru hierarchy with condition (next teacherId)=(current studentId)
For anchor part of query we use condition where teacherId=@searchId.Rows with studentId=@searchId only need when looking up.
r2. Search up thru hierarchy with condition (next studentId)=(current teacherId)
Last (highest) level of recursion r2 point to highest teacher.

Then union output from recursive queries r1 and r2. We take studentId from r1 and teacherId from r2 as target link.
Last step - aggregate with condition dir ('up','down') to get teachers and students list.

See example

declare @searchId int=50;
with r1 as(
  select 0 lvl,'down' dir,@searchId as root,studentId,teacherId
  from hierarchy
  where teacherId=@searchId
  union all
  select  lvl+1 lvl,'down' dir,r.root,t.studentId,t.teacherId
  from r1 r inner join hierarchy t on t.teacherId=r.studentId
)
,r2 as(
  select 0 lvl,'up' dir,@searchId root,studentId,teacherId
   from hierarchy
   where studentId=@searchId
  union all
  select  lvl+1 lvl,'up' dir,r.root,t.studentId,t.teacherId
  from r2 r inner join hierarchy t on r.teacherId=t.studentId
)
select root
 ,max(case when dir='up' and lvl=maxlvl then linkedId end) as high_teacher 
 ,string_agg(case when dir='up' then linkedId end,',')
         within group(order by linkedId) as teachers 
 ,string_agg(case when dir='down' then linkedId end,',')
         within group(order by linkedId ) as students 
from(
select lvl,dir,root,studentId linkedId
  ,0 maxlvl
from r1
  union all
select lvl,dir,root,teacherId linkedId
  ,max(lvl)over() maxlvl
from r2
)a
group by root
;

With test data as

Or for @searchId=5 (There no rows with studentId=5 in table)

Or for @searchId=15

Or for @searchId=25

fiddle