Retrieve hierarchical data

Question

I have the following tables:

Teachers:

+------+--------+
|  id  | deptId |
+------+--------+
|  1   |    2   |
+------+--------+
|  2   |    1   |
+------+--------+

Department:

+------+-----------+
|  id  | contactId |
+------+-----------+
|  1   |     2     |
+------+-----------+
|  2   |     6     |
+------+-----------+

Contact:

+----+--------------------+
| id | nonImportantColumn |
+----+--------------------+
| 2  |   street street    |
+----+--------------------+
| 4  |   street2street    |
+----+--------------------+

SchoolBranch:

+----+-----------+----------------+
| id | contactId | parentBranchId |
+----+-----------+----------------+
| 3  |    2      |     null       |
+----+-----------+----------------+
| 4  |    5      |     3          |
+----+-----------+----------------+
| 5  |    7      |     null       |
+----+-----------+----------------+
| 6  |    2      |     3          |
+----+-----------+----------------+

Given a teacher's ID let's say 2, I am trying to get the below depicted resultset format but don't know how to begin. I am not an expert.

+----------+-----------+----------------+--------------------+
| branchId | contactId | parentBranchId | nonImportantColumn |
+----------+-----------+----------------+--------------------+
|    3     |     2     |      null      |  some street adr   |
+----------+-----------+----------------+--------------------+
|    4     |     5     |        3       |  some street adr   |
+----------+-----------+----------------+--------------------+
|    6     |     2     |        3       |  some street adr   |
+----------+-----------+----------------+--------------------+

The result contains the data for branch id 4 and 6 as well because these branches are child branches of 3 (which is linked to our input teacher ID:2)

I am using SQL Server v 18, in case it helps.

Answer 1

You can use a CTE to create a dataset for SchoolBranch that includes the contactId value for the parent record then simply INNER JOIN to the main dataset to get the data you're after.

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

You can see a working example on db<>fiddle.