Unable to search single number and comma separated string in same query

Question

I am trying to search on specific scenario from last 8 hours but unable to sort it out.

I need to get records from single table where I need to match a comma separated string against two columns.

-Both columns contain single values like 1 or comma separated values like 1,2,3

I need to get records where minimum one AND condition matches for both columns either for single value or comma separated value.

Here is my query

SELECT specialities, ids_origin, id, latitude, longitude 
FROM `ep_restaurant` 
where `specialities` in (2,4,5,32) and `ids_origin` in (106,154,3)

The record is fetching

http://prntscr.com/ntiao7

But it is matching when both columns have same whole set of comma separated values, I also need to get all of those where even single values from both columns match like 2,6 or 2,154 or whole comma separated string like (2,4,5,32) and (106,154,3) matched..

It is not my own database so i cannot change it. Please help me .Thanks.

Answer 1

MySQL has a useful function for such must-avoid use-case.

For example, if you have specialities table:

SELECT r.id, latitude, longitude, r.specialities
  FROM ep_restaurant r
  WHERE exists( SELECT 1 
                  FROM specialities s 
                  WHERE find_in_set(s.id, r.specialities)
                    AND s.id in(2,6)
              )
     OR exists( SELECT 1 
                  FROM <Origins Table> o 
                  WHERE find_in_set(o.<Column Id>, r.ids_origin)
                    AND o.<Column Id> in(106,154)
              )