SQL How to get the records where contains keywords from another table

Question

I need help in SQL to get the records where it contains the keywords from another table

I have a main table and another table for the keywords.

Main Table

ID	Item
11	colored paper
12	red shirt
13	antiblack shoe
14	yellow desk

Keywords Table

Keywords
red
yellow
black
green

Expected Output

ID	item
12	red shirt
14	yellow desk

The expected output is the records that contains the color from the keywords table The item with ID 11 (contain colored) and 13 (contain antiblack) are not included because it is not referred to as "color" meaning

How can I use split each item name and then match it with the keywords table to get the record only fully matched with keywords? Thankyou!

Answer 1

SELECT *
FROM Main
WHERE substr(keywords,1,instr(keywords,' ')) IN(
    SELECT distinct keywords FROM Keywords
)

another way

SELECT m.* FROM main AS m
LEFT JOIN keywords AS k ON m.item LIKE '%'||k.keywords||'%'
WHERE k.keywords IS NOT NULL

code above run in SQLite