CODEWITHSUNDEEP
google-bigquery
Elena Epstein
Elena Epstein

Reputation: 75

BigQuery: Column name is ambiguous even after applying alias

dear Stackoverflow community,

I am trying to run a query to join two datasets on one key using an alias, but the validator shows an error Query error: Column name visit_id is ambiguous

SELECT visit_id as a
FROM `dashboards.test.visits` FULL JOIN `dashboards.test.tracks` on visit_id;

**Schema for `dashboards.test.visits`**

Field name  Type    Mode    
visit_time  TIMESTAMP   NULLABLE    
visit_id    INTEGER NULLABLE    
device_id   INTEGER NULLABLE    
traffic_source  STRING  NULLABLE    
pages_viewed    FLOAT   NULLABLE    

**Schema `dashboards.test.tracks`**

Field name  Type    Mode    
track_time  TIMESTAMP   NULLABLE    
track_id    FLOAT   NULLABLE    
visit_id    FLOAT   NULLABLE

Upvotes: 0

Views: 1472

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

You can use USING to avoid this error

SELECT visit_id
FROM `dashboards.test.visits` 
FULL JOIN `dashboards.test.tracks` 
USING(visit_id);

Upvotes: 1

Daniel Zagales
Daniel Zagales

Reputation: 3034

The ambiguity is to where the column should be sourced from as it can come from both visits and tracks. Try the following:

SELECT a.visit_id
FROM `dashboards.test.visits` a
FULL JOIN `dashboards.test.tracks` b
on a.visit_id = b.visit_id;

Upvotes: 1

Related Questions