How to join to the same table multiple times?

Question

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.

score 18 · Accepted Answer · answered Jul 27 '16 at 09:03

18

The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins. It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned:

SELECT 
    o.name  AS object, 
    p1.name AS parent_1, 
    p2.name AS parent_2, 
    c1.name AS child_1,  
    c2.name AS child_2 
FROM 
    hierarchy_table AS h
  LEFT JOIN name_table AS o   ON h.object_id   = o.name_id
  LEFT JOIN name_table AS p1  ON h.parent_id_1 = p1.name_id  
  LEFT JOIN name_table AS p2  ON h.parent_id_2 = p2.name_id
  LEFT JOIN name_table AS c1  ON h.child_id_1  = c1.name_id 
  LEFT JOIN name_table AS c2  ON h.child_id_2  = c2.name_id ;

answered Jul 27 '16 at 09:03

ypercubeᵀᴹ

97,895
13
214
305

Yes, so columns are nullable, so I do need the left join. Thank you. – jase81 Jul 28 '16 at 02:39
For some reason, I just wasn't getting a grasp of aliasing until this answer. It may be worth noting that this won't work in MS Access (maybe others). I was trying to do something very similar and I kept getting a syntax error. Access requires nesting of Joins with (). – doubleJ May 14 '18 at 16:17
@doubleJ yes, Access is notorious for this need of extra parentheses whenever there are more than 1 joins in the FROM. – ypercubeᵀᴹ May 14 '18 at 16:46
It might be worth noting that the AS in the join clauses appears to be optional in postgres. But maybe that's bad practice? – Phlucious Sep 10 '21 at 21:21
@Phlucious true, it's optional in all flavours of SQL (with very few exceptions). And yes, I think it's good practice to use it, both in SELECT list and the FROM clause – ypercubeᵀᴹ Sep 10 '21 at 22:13

score 3 · Answer 2 · edited Jul 27 '16 at 09:06

3

You can use alias name for tables involved in query.

select b.name object, c.name parent_1, d.name parent_2 
from hierarchy_table a, name_table b, name_table c, name_table d
where a.object_id = b.name_id 
  and a.parent_id_1 = c.name_id 
  and a.parent_id_2 = d.name_id

edited Jul 27 '16 at 09:06

ypercubeᵀᴹ

97,895
13
214
305

answered Jul 27 '16 at 04:01

Isaac A. Nugroho

539
3
8

2

You'd better not use the old-style (old means more than 20 years) joins. The ANSI syntax is more readable and less error-prone. – András Váczi Jul 27 '16 at 13:14

score 0 · Answer 3 · answered Sep 19 '19 at 04:28

TL&DR: Alias should be used when you want to join to the same table multiple times

Rational:

In Postgres, typically people join one column in one table to another column in a different table. This was brilliant from a design perspective as the normal use case. When you want to join additional columns you will need to use aliases (best practice).

HOWTO:

When performing an INNER JOIN, make sure to add an AS clause along with the name.

Example

INNER JOIN ipaddresses as child_address ON ipaddress_relations.ipaddress_id = child_address.ipaddressid

If you notice the 'accepted' answer does this above.

SELECT 
    o.name  AS object, 
    p1.name AS parent_1, 
    p2.name AS parent_2, 
    c1.name AS child_1,  
    c2.name AS child_2 
FROM 
    hierarchy_table AS h
  LEFT JOIN name_table AS o   ON h.object_id   = o.name_id
  LEFT JOIN name_table AS p1  ON h.parent_id_1 = p1.name_id  
  LEFT JOIN name_table AS p2  ON h.parent_id_2 = p2.name_id
  LEFT JOIN name_table AS c1  ON h.child_id_1  = c1.name_id 
  LEFT JOIN name_table AS c2  ON h.child_id_2  = c2.name_id ;

How to join to the same table multiple times?

3 Answers3