Union ALL multiple query Google BigQuery

2
votes

I have three queries with the same column count and structure, does Google BigQuery support union all? I know it supports union but I need it to merge the result of the query into one result.

I have tried:

SELECT * 
FROM 
(SELECT a.a AS a, a.b AS b, d.c AS c FROM table_a a JOIN table_d d on d.a = a.a),
(SELECT c.a AS a, c.b AS b, d.c AS c FROM table_c c JOIN table_d d on d.a = ca.a),
(SELECT b.a AS a, b.b AS b, d.c AS c FROM table_b b JOIN table_d d on d.a = b.a)

I get the following error:

Union results in ambiguous schema [d.c] is ambiguous and is aliasing multiple fields

Thanks For your help.

1
google-bigquery
Can you share a sample of the dataset publicly? Stackoverflowers will be happy to help with a working query - and it will be easier to test with the actual data. - Felipe Hoffa
Thanks for your reply, I can't really share a sample of the dataset, it's a normal table not nested, and the data is loaded in from a csv file, into BigQuery. If that helps - user3690992
Please share your dataset id name, and proper queries, and BQ team will probably pick this up, and will be able to answer you. - Pentium10
I will see what I can do. At the moment I just need to know how to do a union all in BigQuery. - user3690992
Are each of the subqueries working in isolation? - Rohit

1 Answers

1
votes

Must you know if the data are come from table a, b or c? If you don't need to know, you can try the following query:

select *
from
(select a, b
from table_a, table_b, table_c) as a
join
(select a, c
from table_d) as d 
on a.a = d.a