Run Where clause from another table in Bigquery

bigquery with statement
bigquery subquery
bigquery select * from multiple tables
bigquery sql
bigquery create table
bigquery substring
bigquery table_query
bigquery struct

How can I run where clause from another table?

For example:

table1:
**sentence**
----------
AB      
AA      
AC      
AEF     
AHF 

and but the where condition comes from another

table 2
**text**

sentence like '%A%' and sentence like '%B%'

sentence like '%A%' and sentence like '%C%'

sentence like '%A%' and sentence like '%E%' and sentence like '%F%'

I would like the result like this way:

result


AB
AC
AEF

How can I do this? Thanks

Standard SQL Query Syntax | BigQuery, This is true whether or not your query contains a WHERE clause on the _TABLE_SUFFIX pseudo column to filter out the view. Currently, cached results are not  Another useful role of the WITH clause is to break up more complex queries into a WITH SELECT statement and WITH clauses, where the less desirable alternative is writing nested table subqueries. If a WITH clause contains multiple subqueries, the subquery names cannot repeat. BigQuery supports WITH clauses in subqueries, such as table subqueries, expression subqueries, and so on. WITH q1 AS (my_query) SELECT * FROM (WITH q2 AS (SELECT * FROM q1) SELECT * FROM q2) The following are scoping

You could use a join and concat with proper logic condition

  select  table2.text
  from table2 
  inner join table1  on ( (table2.text like concat('%','A','%' )
                          and table2.text like concat('%','B','%' ) )
                      or ( table2.text like concat('%','A','%' )
                          and table2.text like concat('%','C','%' ) )  
                      or  ( table2.text like concat('%','A','%' )
                          and table2.text like concat('%','E','%' )
                          and  table2.text like concat('%','F','%' ))  ) 

Querying multiple tables using a wildcard table, As a Google BigQuery data warehouse user, you are able to create tables by Next, Compose a Query just like normal, but before executing it via the Run  Using _TABLE_SUFFIX can greatly reduce the number of bytes scanned, which reduces the cost of running your queries. Filters on _TABLE_SUFFIX that include subqueries cannot be used to limit the number of tables scanned for a wildcard table.

Below is for BigQuery Standard SQL

#standardSQL
SELECT text, sentence
FROM `project.dataset.table1`,
  `project.dataset.table2`, 
  UNNEST(REGEXP_EXTRACT_ALL(text, "'(.*?)'")) re
GROUP BY sentence, text
HAVING MIN(sentence LIKE re)  

If to apply to data from your question as below

#standardSQL
WITH `project.dataset.table1` AS (
  SELECT 'AB' sentence UNION ALL
  SELECT 'AA' UNION ALL
  SELECT 'AC' UNION ALL
  SELECT 'AEF' UNION ALL
  SELECT 'AHF' 
), `project.dataset.table2` AS (
  SELECT "sentence like '%A%' and sentence like '%B%'" text UNION ALL
  SELECT "sentence like '%A%' and sentence like '%C%'" UNION ALL
  SELECT "sentence like '%A%' and sentence like '%E%' and sentence like '%F%'" 
)
SELECT sentence, text
FROM `project.dataset.table1`,
  `project.dataset.table2`, 
  UNNEST(REGEXP_EXTRACT_ALL(text, "'(.*?)'")) re
GROUP BY sentence, text
HAVING MIN(sentence LIKE re)   

result will be

Row sentence    text     
1   AB          sentence like '%A%' and sentence like '%B%'  
2   AEF         sentence like '%A%' and sentence like '%E%' and sentence like '%F%'  
3   AC          sentence like '%A%' and sentence like '%C%'

How to Create a Table From a Query in Google BigQuery, Typically in BigQuery, this occurs when you're gathering data from multiple tables or even across datasets, and this is where the power of using a UNION comes  Next, Compose a Query just like normal, but before executing it via the Run Query button, click the Show Options button. From here, you’ll see the Destination Table section: Simply click Select Table and the popup will ask you to select the Project, Dataset, and specify the Table Name to use as your destination table. For our purposes, we

How to UNION Queries in Google BigQuery, Using the sample datset; Using Standard SQL; Query multiple tables Every query you run contributes to your monthly data-processing allowance. When you formulate a query, select the relevant fields within the SELECT statement. By not  Jobs are actions that BigQuery runs on your behalf to load data, export data, query data, or copy data. When you use the GCP Console, the classic BigQuery web UI, or the CLI to load, export, query, or copy data, a job resource is automatically created, scheduled, and run.

BigQuery cookbook - Analytics Help, Below are 13 video tutorials to get you up and running – but to really learn this (see channel above), if you'd rather use a column name different from the one For the FROM parameter, in BigQuery there are 3 layers included in each table name: Adding to our basic SELECT statement above, we'd layer on a WHERE​  If a row in the table to be updated joins with exactly one row from the FROM clause, then the row is updated. If a row in the table to be updated joins with more than one row from the FROM clause, then the query generates the following runtime error: UPDATE/MERGE must match at most one source row for each target row.

Learning BigQuery SQL, Google's BigQuery is a very powerful "No Ops" database that operates in json, or avro because you need to move your data to another tool, such as Cloud SQL. a much more cost effective way to query all fields in a table on BigQuery. So rather than execute a lookup query with a LIMIT clause, keep  Using Subqueries in the Select Statement. When a subquery is placed within the column list it is used to return single values. In this case, you can think of the subquery as a single value expression. The result returned is no different than the expression “2 + 2.” Of course, subqueries can return text as well, but you get the point!

Comments
  • It sounds like you want dynamic SQL. You cannot do this with a simple select. You might want to re-visit your data structure.