Query to find all members of multi level many to many relationships and also the top most member of the hierarchy

Question

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

Following are the expected outputs provided the selected input in where clause

Table & data script for quick reference

Answer 1

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.