Re: Conditional Lookup Table with Like

On Fri, Feb 17, 2017 at 8:33 AM, sqlQuestions <ryanpgodfrey(at)gmail(dot)com>
wrote:

> Hi David,
>
> You got me really close, but I'm still not getting the correct results due
> to what I've learned is called cartesian product. The result set is being
> multiplied because of extra rows. Have you seen this before?
>

​You added a third table ... the fact that my answer doesn't work isn't
surprising.​

​You might need to go back to using a correlated subquery:

SELECT ...
COALESCE ((SELECT ... FROM table3 WHERE table3 = table1[outer reference]),
table2.category)
FROM table1 JOIN table2

That, or modify the JOIN clause as noted below:

> Here is my query with an example schema. It should only return the first 6
> rows in table1 with whichever category is correct.
>
> SELECT table1.product_code, table1.date_signed, table1.description,
> CASE
> WHEN lower(table1.description) LIKE ('%' || lower(table3.lookup_value) ||
> '%')
> THEN table3.category
> ELSE table2.category
> END
> FROM table1
> LEFT JOIN table2 ON table2.psc_code = table1.product_code
>

> LEFT JOIN table3 ON table3.psc_code = table1.product_code
>

​This join above must return zero or one records to avoid a duplicating
rows in table1. To do so a match on product_code/psc_code is
insufficient. You need to move the "table1.description LIKE [...]"
expression here. When the expression is false the row from table3 will be
all nulls. As shown above you can use COALESCE to pick the table3 value if
its non-null otherwise pick the table2 value.

I would highly suggest you define primary keys on your tables...

David J.