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...
