I am a beginner learning query statements. I'm asking a question because I don't understand this part.
- TABLE 1 has a PRIMARY_KEY column designated as an array column.
- 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.