Reputation: 2013
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
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