SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Teachers
(
  id INT,
  deptId INT
)

CREATE TABLE Department
(
  id INT,
  contactId INT
)

CREATE TABLE Contact
(
  id INT,
  NonImportantColumn VARCHAR(255)
)

CREATE TABLE SchoolBranch
(
  id INT,
  contactId INT,
  parentBranchId INT
)
INSERT INTO Teachers
VALUES (1, 2), (2, 1)

INSERT INTO Department
VALUES (1, 2), (2, 6)

INSERT INTO Contact
VALUES (2, 'Street Street'), (4, 'Street2 Street')

INSERT INTO SchoolBranch
VALUES (3, 2, NULL), (4, 5, 3), (5, 7, NULL), (6, 2, 3)
10 rows affected

WITH CTE AS (
  SELECT id, contactId, parentBranchId, NULL AS parentContactId
  FROM SchoolBranch
  WHERE parentBranchId IS NULL
  UNION ALL
  SELECT sb.id, sb.contactId, sb.parentBranchId, p.contactId AS parentContactId
  FROM SchoolBranch sb
  INNER JOIN CTE p ON p.id = sb.parentBranchId
)

SELECT sb.id AS branchId
  , c.id AS contactId
  , sb.parentBranchId
  , c.NonImportantColumn
FROM Teachers t
INNER JOIN Department d ON d.id = t.deptId
INNER JOIN Contact c ON c.id = d.contactId
INNER JOIN CTE sb ON (sb.contactId = c.id) OR (sb.parentContactId = c.id)
WHERE t.id = 2


branchId
contactId
parentBranchId
NonImportantColumn


3
2
null
Street Street

4
2
3
Street Street

6
2
3
Street Street