Questions about the trino all_match function

29 Views Asked by At

I am a beginner learning query statements. I'm asking a question because I don't understand this part.

  1. TABLE 1 has a PRIMARY_KEY column designated as an array column.
  2. I want to check and print whether all words are included in the primary_key column in the PRODUCT_NAME column in TABLE 2.

Before querying the array column in table 1, I manually entered array['amazon','table'] and searched to see if it worked well. At this time, the price was good.

However, when I search the table with a subquery or cross join, no value comes out. The reason for subquery or cross join is because there is no connected value.

Below is an example I created.

This is a successful result.

-- WORK IT
WITH CTE AS(
SELECT T2.*, CASE
WHEN ALL MATCH(ARRAY['AMAZON','TABLE'],ELEMENT -> T2.PRODUCT_NAME LIKE '%'||ELEMENT||'%')THEN 'AMAZON_CHAIR' END AS PRODUCT_LINE
FROM TABLE2 AS T2
)
SELECT * FROM CTE
WHERE PRODUCT_LINE NOT LIKE ''

If i do this, no value will be output.

WITH CTE AS (SELECT T2.*,
CASE WHEN ALL_MATCH((SELECT PRIMARY_KEY FROM TABLE1),ELEMENT -> T2.PRODUCT_NAME LIKE '%'||ELEMENT||'%') THEN 'AMAZON_CHAIR' END AS PRODUCT_LINE
FROM TABLE2 AS T2
)
SELECT * FROM CTE
WHERE PRODUCT_LINE NOT LIKE '' 
WITH CTE AS (SELECT T2.*,
CASE WHEN ALL_MATCH(T1.PRIMARY_KEY,ELEMENT -> T2.PRODUCT_NAME LIKE '%'||ELEMENT||'%') THEN 'AMAZON_CHAIR' END AS PRODUCT_LINE
FROM TABLE2 AS T2
CROSS JOIN TABLE1 AS T1
)
SELECT * FROM CTE
WHERE PRODUCT_LINE NOT LIKE '' 

What did I do wrong? I looked through the documentation, but I'm not sure what's wrong. I'm curious how best to solve this.

I want to match the array columns in table 1 to each sentence in table 2 and output only when all words are included.

0

There are 0 best solutions below