CODEWITHSUNDEEP
hivehiveql
Pheonix
Pheonix

Reputation: 95

Multiple table join query - IDs and data tables

SOLVED turns out it was my where clause which was throwing off the results, I changed this out and added the where clause to the ON statement

I need some help.

I have a table with 25 million IDs and 4 tables with IDs and data. I need to create a new table with these 25 million IDs as well as the associated table data from the 4 tables. Each data table will not contain the full 25 million IDs. So as an example;

ID Table:

ID
A
B

Table 1

ID measure_a measure_b
B 1 3

Table 2

ID measure_f measure_g
A 3 4

etc..

Expected output:

ID measure_a measure_b measure_f measure_g
A 3 4 NULL NULL
B NULL NULL 1 3

The most important thing is the 25 million IDs are in the final table. I've tried multiple joins but end up with a hugely reduced number of IDs which I believe is due to the IDs which don't match on the join condition being filtered out.

Any help is greatly appreciated.

Upvotes: 0

Views: 244

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You would use left joins:

select ids.id, t1.measure_a, t1.measure_b, t2.measure_f, t2.measure_g
from ids left join
     table1 t1
     on ids.id = t1.id left join
     table2 t2
     on ids.id = t2.id;

Upvotes: 2

Related Questions