CODEWITHSUNDEEP
sqlgoogle-bigquery
Capacytron
Capacytron

Reputation: 3739

BigQuery select, join from multiple datasets and avoid name conflicts

Imagine I have several datasets and tables.

Format: dataset.table.field

dataset01.table_xxx.field_z
dataset02.table_xxx.field_z

I try to write smth like

select 
dataset01.table_xxx.field_z as dataset01_table_xxx_field_z,
dataset02.table_xxx.field_z as dataset02_table_xxx_field_z
from dataset01.table_xxx
join dataset02.table_xxx on dataset02.table_xxx.field_z = dataset01.table_xxx.field_z

to avoid conflicting names BigQuery says that dataset01.table_xxx.field_xxx is unrecognised name in SELECT clause.

it complains about unrecognised name in join clause too. Query works if I remove dataset01, dataset02 from SELECT clause and on condition

What is the right way to refer fields in such case?

Upvotes: 1

Views: 362

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

select 
  t1.field_z as dataset01_table_xxx_field_z,
  t2.field_z as dataset02_table_xxx_field_z
from dataset01.table_xxx t1
join dataset02.table_xxx t2
on t2.field_z = t1.field_z

Upvotes: 3

Related Questions