SQL: How to join hierarchical table relationship

Question

I have 3 tables which are Department, Office, Division

Supposed flow is: Department has Offices has Divisions

But there are instances that Divisions reports directly to Departments

Example Data

Department   Office                Division
[id][name]   [dept_id][id][name]   [dept_id][off_id][id][name]
[ 1][A   ]   [     1 ][ 1][D   ]   [     1 ][    2 ][ 1][G   ]
[ 2][B   ]   [     1 ][ 2][E   ]   [     3 ][ NULL ][ 2][H   ]
[ 3][C   ]   [     3 ][ 3][F   ]   [     3 ][   3  ][ 3][I   ]

Desired Output to display all Divisions:

OUTPUT
[AS dept_name][AS off_name][AS div_name]
[      A     ][     E     ][     G     ]
[      C     ][    NULL   ][     H     ]
[      C     ][     F     ][     I     ]

My current Query is:

    SELECT * FROM `Division` A 
    RIGHT JOIN `Office` B ON A.`off_id` = B.`id` 
    RIGHT JOIN `Department` C ON A.`dept_id` = C.`id`;

my query is wrong, so i'm looking for a way to join those 3 table to achieve the desired output

my query is wrong, so i'm looking for a way to join those 3 table to achieve the desired output — Roi
– Roi, Commented May 26, 2017 at 8:42
I think you just need to join with divisions two times with two different aliases, e.g. A1 and A2. — Thomas Koelle
– Thomas Koelle, Commented May 26, 2017 at 8:43

Alexander · Accepted Answer · 2017-05-26 08:52:14Z

1

If you want to to display all divisions, it is necessary to use LEFT JOIN.

SELECT
  Department.`name` AS dept_name, 
  Office.`name` AS off_name,
  Division.`name` AS div_name
FROM
  Division 
  LEFT JOIN Office ON Division.off_id = Office.id
  LEFT JOIN Department ON Division`.dept_id = Department.id;

answered May 26, 2017 at 8:52

Alexander

4,5527 gold badges31 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: How to join hierarchical table relationship

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related