CODEWITHSUNDEEP
sqlgoogle-bigquery
Winnie Huang
Winnie Huang

Reputation: 11

Run Where clause from another table in Bigquery

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

Upvotes: 1

Views: 404

Answers (3)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

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%'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271013

It sounds like you need dynamic SQL. I would suggest fixing your data model so this is not needed. Instead of storing a where condition, I think you can use regular expressions instead:

  • 'A.*B|B.*A'
  • 'A.*C|C.*A'
  • 'A.*E.*F|A.*F.*E|E.*A.*F|E.*F.*A|F.*A.*E|F.*E.*A'

Note: There are alternative ways to express the logic but I think this makes it clear.

Then you can do:

select t1.sentence, t2.pattern
from table1 t1 join
     table2 t2
     on regexp_contains(t1.sentence, t2,pattern);

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133400

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','%' ))  )

Upvotes: 0

Related Questions