Presto - Concat multiple tables using unique identifier

Question

I have multiple tables in the following format:

table users -

ID lang
1  EN
2  EN
3  DE

table A -

ID event1 event2
1  5      1
2  null   1
3  11     null

table B -

ID event1 event10
1  2      1
3  2     null

so after concat/join the tables on ID column my final table would look like this:

final_table -

ID lang A_event1 A_event2 B_event1 B_event10
1  EN   5        1        2        1
2  EN   null     1        null     null
3  DE   11       null     2        null

So I have multiple issues here, first how to properly do the joins so that aliases would match table names and have final unique column names even though the events have same naming inside the columns, and also I would like all the missing values would also have null values (like table B that does not have user ID = 2).

My tries so far were not successful as the column names would be duplicated without unique IDs and missing values were not filled with nulls properly.

example for what I already tried:

select t1.*, t2.*, t3.*
from users t1
left join
A t2
using (ID)
left join
B t3
using (ID)

I can construct the query programmatically to provide flexability, but I would like to know the proper syntax for such case.

Thanks.

Your syntax looks fine, except that you should list the columns explicitly in the `select`. — Gordon Linoff, Dec 12 '19 at 12:06

score 1 · Accepted Answer · answered Dec 12 '19 at 09:38

Your attempt with two left joins looks quite good. I would, however suggest not using the using(id) syntax to join the tables: with 3 tables involved, it is ambiguous to which id column you are referring, which could lead to missing records in the resultset:

select
    u.id,
    u.lang,
    ta.event1 A_event1,
    ta.event2 A_event2,
    tb.event1 B_event1,
    tb.event110 B_event10
from users u
left join tableA ta on ta.id = u.id
left join tableB tb on tb.id = u.id

I don't see how this query would generate duplicate ids in the resultset (as long as the ids are unique in each table, as shown in your sample data).

score 0 · Answer 2 · answered Dec 12 '19 at 12:12

If the non-id columns in the tables were unique, then you could express this as:

select *
from users u left join
     A 
     using (ID) left join
     B
     using (ID);

The id means the same thing in the three tables, so it is appropriate to use using. In fact, using is very handy when working with outer joins (although more so with full join).

I'm not a big fan of using select *. And it is not appropriate in this case because the columns are not unique. So a fine way to write the query is:

select u.*,
       a.event1 as a_event1, a.event2 as a_event2,
       b.event1 as b_event1, b.event10 as b_event10
from users u left join
     A 
     using (ID) left join
     B
     using (ID);

Presto - Concat multiple tables using unique identifier

2 Answers2