24

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.

0

3 Answers 3

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 ;
5
  • Yes, so columns are nullable, so I do need the left join. Thank you.
    – jase81
    Jul 28, 2016 at 2: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, 2018 at 16:17
  • @doubleJ yes, Access is notorious for this need of extra parentheses whenever there are more than 1 joins in the FROM. May 14, 2018 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, 2021 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 Sep 10, 2021 at 22:13
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
1
  • 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. Jul 27, 2016 at 13:14
0

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.