0

Admins, please note although it shares the same structure from the question I posted earlier about hierarchical data but it is a completely different 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 teacher's linked branch, which should be BranchID:3 row but I am receving an extra row for BranchID:6 because it also shares the same contactId.

I am using SQL Server v 18, in case it helps. I have tried this query

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
    )

but it returns two rows instead of one. Here is dbfiddle link

2
  • From your example, the inner query returns 2, therefore, the outer query executes like this: select b.* from SchoolBranch b where b.contactId = 2. Since the table SchoolBranch has two rows with contactId equals 2, your final resultset has two rows. I can't see the problem. Did I miss something? – Ronaldo Apr 30 '20 at 11:28
  • Your output is correct.On what basis you should get branchid 3 and not 6.Tell the requirement/ – KumarHarsh May 6 '20 at 9:02

Your Answer

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

Browse other questions tagged or ask your own question.