CODEWITHSUNDEEP
google-bigquery
chip9munk
chip9munk

Reputation: 47

BigQuery: select from regexp_match where field in other table

How can I select some fields from multiple tables, while cross checking with some field from another table. I want info from udids that are listed in another table.

Example will make it clear:

SELECT udid, score 
FROM (TABLE_QUERY(data_one,'REGEXP_MATCH(table_id, "Data_2014_05_*")'))
WHERE udid IN (SELECT udid FROM data_two.udid_with_high_levels)
GROUP BY udid

Is it possible to do this with "WHERE IN"? And how? I am getting the following error: "JOIN (including semi-join) and UNION ALL (comma) may not be combined in a single SELECT statement. Either move the UNION ALL to an inner query or the JOIN to an outer query."

How could it be done with JOIN, since my first table comes from regexp_match, what is the syntax?

Thank you!

Upvotes: 2

Views: 1759

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59175

You can do a sub-select.

This query over public data shows the same error:

SELECT title, SUM(requests) c
FROM (TABLE_QUERY([fh-bigquery:wikipedia],'REGEXP_MATCH(table_id, "pagecounts_2014*")')) 
WHERE title IN (
  SELECT title
  FROM [fh-bigquery:wikipedia.wikipedia_views_201308_en_top_titles_views]
  GROUP BY title) 
GROUP BY 1
ORDER BY 2

Adding an outer select to the table selection fixes the issue:

SELECT title, SUM(requests) c
FROM (SELECT title, requests 
  FROM (TABLE_QUERY([fh-bigquery:wikipedia],'REGEXP_MATCH(table_id, "pagecounts_2014*")')))
WHERE title IN (
  SELECT title
  FROM [fh-bigquery:wikipedia.wikipedia_views_201308_en_top_titles_views]
  GROUP BY title) 
GROUP BY 1
ORDER BY 2

Upvotes: 3

Related Questions