Re: Conditional Lookup Table with Like

On Thu, Feb 16, 2017 at 3:19 PM, sqlQuestions <ryanpgodfrey(at)gmail(dot)com>
wrote:

> I'm having trouble with a weird query.
>
> *table1*
> code
> description
> category
>
> *table2*
> code
> lookup_value
> category
>
> 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 || '%')
THEN table2.category
ELSE table1.category
END
FROM table1
LEFT JOIN table2 USING (code);

David J.
​