CODEWITHSUNDEEP
sqlgoogle-bigquery
d-_-b
d-_-b

Reputation: 23151

query multiple tables that use the same fields

I'm using bigquery to query two datasets that share a schema:

SELECT
    *
FROM (
  SELECT
    name as name,
    REGEXP_EXTRACT_ALL(details, r"(value=\w+)") as regex
  FROM
    Dataset.table1,
    Dataset.table2 )
WHERE
  ARRAY_LENGTH(regex) > 0

But I get the error that the "name" field is ambiguous, which makes sense since I am not specifying either of the two tables.

Is there a way to query shared fields from 2+ tables at once like this?

Upvotes: 1

Views: 261

Answers (2)

Andrews B Anthony
Andrews B Anthony

Reputation: 1381

You can use tablename.columnname Use the below query

SELECT
    *
FROM (
  SELECT
    table1.name as name,
    REGEXP_EXTRACT_ALL(details, r"(value=\w+)") as regex
  FROM
    Dataset.table1,
    Dataset.table2 )
WHERE
  ARRAY_LENGTH(regex) > 0

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Because of use of ARRAY_LENGTH and REGEXP_EXTRACT_ALL it looks like you are using BigQuery Standard SQL mode.
At the same time you are still using Legacy SQL notion of UNION ALL via use of comma - which is not a case for Standard SQL!
You should use explicit UNION ALL
Hope this helps!

Upvotes: 2

Related Questions