I want to pull the code, description and category from table1. Some, not all, codes from table1 have entries in table2, and some have multiple. In table2, where codes are equal, and when the lookup_value string is found inside the description, I want to pull the category from table2 instead of the category from table1.
I was thinking about a case statement, but can't figure out the syntax. I appreciate any ideas that would help me out. Thanks a lot!
SELECT table1.code, table1.description, CASE WHEN EXISTS ( SELECT 1 FROM table1, table2
The reference to table1 in the from clause here seems wrong - usually you'd use the outer queries' table1 reference as part of a correlated subquery.
WHERE table1.code = table2.code AND table1.description LIKE '%table2.lookup_value%' LIMIT 1
In a correlated subquery within an EXISTS the LIMIT 1 is superfluous
) THEN table2.category ELSE table1.category END AS category FROM table1
In any case the subquery seems unnecessary..
SELECT code, description,
CASE WHEN table2.category IS NULL
THEN table1.category
WHEN description LIKE ('%' || lookup_value || '%')