CODEWITHSUNDEEP
google-bigquery
Username
Username

Reputation: 3663

Column name ambiguous when trying to query multiple tables in BigQuery

I want to query multiple tables, all with the same column names in the same order, and combine the results. 

SELECT SUBSTR(arrest_date, 0, 4) arrest_year, *
FROM 
  `OBTS.circuit11`,
  `OBTS.circuit15`,
  `OBTS.circuit17`,
  `OBTS.circuit19`
WHERE 
  init_statute LIKE '%3%22%32%' OR
  init_statute LIKE '%3%22%34%' OR
  LOWER(init_charge_descrip) LIKE '%suspend%';

When I run this BigQuery gives me the following error.

Column name init_statute is ambiguous at [8:3]

How do I query these tables and combine all the resulting rows into one set of results?

Upvotes: 2

Views: 896

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172954

I think you are looking for UNION ALL vs CROSS JOIN (note: comma in BigQuery Standard SQL is used for express CROSS JOIN)

So, you most likely looking for below 

SELECT SUBSTR(arrest_date, 0, 4) arrest_year, * 
FROM (
  SELECT * FROM `OBTS.circuit11` UNION ALL
  SELECT * FROM `OBTS.circuit15` UNION ALL
  SELECT * FROM `OBTS.circuit17` UNION ALL
  SELECT * FROM `OBTS.circuit19`
)
WHERE 
  init_statute LIKE '%3%22%32%' OR
  init_statute LIKE '%3%22%34%' OR
  LOWER(init_charge_descrip) LIKE '%suspend%'

Upvotes: 4

Related Questions