0

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.

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.