9

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.

11

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 ;
|improve this answer|||||
  • Yes, so columns are nullable, so I do need the left join. Thank you. – jase81 Jul 28 '16 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 '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
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
|improve this answer|||||
  • 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. – dezso Jul 27 '16 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 ;
|improve this answer|||||

Your Answer

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

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