Join a table with others two on BigQuery

Question

What I have are 3 tables, one is a huge table, and the other has the reference that I need query the lines.

For example:

Table1:        Table2:     Table3:
|value|num|    |value|     |value|
| AAA | 2 |    | AAA |     | BBB |
| BBB | 6 |    | CCC |     | EEE |
| CCC | 3 |
| DDD | 7 |
| EEE | 1 |

It should return:

|value|num|
| AAA | 2 |
| BBB | 6 |    
| CCC | 3 |
| EEE | 1 |

What I need thought that I needed to do is:

Select *
from [table1] as A
join [table2] as B
on A.value=B.value
join [table3] as C
on A.value=C.value

I am working with the Bigquery, and I do not know if I have any limitation.

Thank you in advance.

not clear what you want here... values in Table1 that are present in Table2 and Table3? — Felipe Hoffa, Aug 25 '15 at 18:58
SELECT * FROM (query 1), (query 2); This is exactly what i needed — user2977500, Aug 26 '15 at 19:57

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

I think you are close. But looks like you want an UNION

Select *
from [table1] as A
   join [table2] as B
   on A.value=B.value
UNION
Select *
from [table1] as A
   join [table3] as C
   on A.value=C.value

EDIT

looks like bigquery doesnt support UNION but have this workaround.

SELECT * FROM (query 1), (query 2);

It does the same thing as :

SELECT * from query1 UNION select * from query 2

edited May 23 '17 at 12:06

Community

1
1

answered Aug 21 '15 at 18:54

Juan Carlos Oropeza

47,252
12
78
118

Join a table with others two on BigQuery

1 Answers1