CODEWITHSUNDEEP
mysql
Samra
Samra

Reputation: 2013

Mysql subquery not working properly with comma-separated value in column

I have a MySQL table Mapdetail that has resultTypeIds column storing a comma separated list of ids:

MapDetails

=============================
mapheaderid     |    resultTypeIds
=============================
54              |    1,4,-9999

Now, when I try it as a subquery, it does not return me the correct result while if I hard-code (1,4,-9999) it works fine

WRONG - RETURNING 1 ROW ONLY

select * from resulttypes rt where rt.resulttypeid in
        (select md1.resulttypeids from mapdetail md1                
            where md1.mapheaderid = 54)

CORRECT - RETURNING 2 ROWS

select * from resulttypes rt where rt.resulttypeid in (1,4,-9999)

Upvotes: 2

Views: 1055

Answers (1)

Nishanth Matha
Nishanth Matha

Reputation: 6081

If you can't re-structure your DB (as mentioned in Comments). Try using FIND_IN_SET instead of IN

Try something like:

select * from resulttypes rt where FIND_IN_SET (rt.resulttypeid,
            (select md1.resulttypeids from mapdetail md1                
            where md1.mapheaderid = 54))

Upvotes: 3

Related Questions