Using case in where to filter for different table?

In MySQL 5.7 I want to filter after a different table column in dependence of a condition beeing true or not.

My initial thought is using a CASE statement that is using a different WHERE condition:

SELECT *
FROM example
JOINS ...
WHERE 
    -- brands or market?
    CASE STRCMP ('->', SUBSTRING('Brandname',1,2))
       WHEN 0 THEN mm.ID = 316 -- 'MARKET'
       ELSE b.ID_PARENT IN ('316') -- 'BRAND'
    END
    AND -- other filters
    ...

Unfortunatelly this does not work, the query runs forever instead of 100ms with a direct replacement of the case statement:

SELECT *
FROM example
JOINS ...
WHERE 
    b.ID_PARENT IN ('316') -- 'BRAND'
    AND -- other filters
    ...

It looks like it should work, but it does not. What could be the issue here?

My question is similar to this, but not the same: Using CASE in combination with WHERE to specify different columns to filter by



Read more here: https://stackoverflow.com/questions/67932989/using-case-in-where-to-filter-for-different-table

Content Attribution

This content was originally published by Merlin at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: