BigQuery: select from regexp_match where field in other table

Question

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!

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2014-05-27T08:29:41

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

BigQuery: select from regexp_match where field in other table

1 Answers