I have two tables, "hierarchy_table" and "name_table".
The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id.
| object_id | parent_id_1 | parent_id_2 | child_id_1 | child_id_2 |
-----------------------------------------------------------------------------
| 1234 | 9999 | 9567 | 5555 | 5556 |
-----------------------------------------------------------------------------
Each object id in the hierarchy_table has an entry in the name_table:
| name_id | name |
--------------------------
| 1234 | ABCD |
--------------------------
| 9999 | ZYXW |
--------------------------
| ...
How do I join each id in the hierarchy_table to the name_table multiple times so that I can have a result where every name is populated?
Like this:
| object | parent_1 | parent_2 | child_1 | child_2 |
-----------------------------------------------------------------------------
| ABCD | ZYXW | BBBB | CCCC | DDDD |
-----------------------------------------------------------------------------
Note: the table names in the example are just for clarity / simplicity, the real names have proper names.
Best Answer
The
hierarchy_table
has 5 columns that all reference thename_table
, so you need 5 joins. It may be better to useLEFT
joins instead ofINNER
, in case some of these columns are nullable and you still want the rows returned: