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,
  name VARCHAR(255)
)
INSERT INTO Teachers
VALUES (1, 2), (2, 1), (3, 6)

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

INSERT INTO Contact
VALUES (2, 'New York, USA'), (4, 'Sydney, Australia')

INSERT INTO SchoolBranch
VALUES (3, 2, NULL, 'Headoffice branch'), (4, 5, 3, 'Street1 branch'), (5, 7, 7, 'Some other head'), (6, 2, NULL, 'Street2 branch')
11 rows affected

select * from Teachers
select * from Department
select * from Contact
select * from SchoolBranch
select b.* from SchoolBranch b where b.contactId = (
	select c.id from  Contact c 
		inner join Department d on c.id = d.contactId
		inner join Teachers t on d.id = t.deptId
	where t.id = 2
	)


id
deptId


1
2

2
1

3
6




id
contactId


1
2

2
6




id
NonImportantColumn


2
New York, USA

4
Sydney, Australia




id
contactId
parentBranchId
name


3
2
null
Headoffice branch

4
5
3
Street1 branch

5
7
7
Some other head

6
2
null
Street2 branch




id
contactId
parentBranchId
name


3
2
null
Headoffice branch

6
2
null
Street2 branch

id	contactId	parentBranchId	name
3	2	null	Headoffice branch
4	5	3	Street1 branch
5	7	7	Some other head
6	2	null	Street2 branch