How to use values returned from mysql stored function with in() function?

Question

I have a field named 'dealBusinessLocations' (in a table 'dp_deals') which contain some ids of another table(dp_business_locations) in comma separated format.

dealBusinessLocations
----------------------
0,20,21,22,23,24,25,26

I need to use this values within an in() function of a query.

like

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (d.dealBusinessLocations) ;

Sine mysql doesn't support any string explode function, I have created a stored function

delimiter //
DROP FUNCTION IF EXISTS BusinessList;
create function BusinessList(BusinessIds text) returns text deterministic
BEGIN
  declare i int default 0;
  declare TmpBid text;
  declare result text default '';
  set TmpBid = BusinessIds;
  WHILE LENGTH(TmpBid) > 0 DO
           SET i = LOCATE(',', TmpBid);
           IF (i = 0)
                   THEN SET i = LENGTH(TmpBid) + 1;
           END IF;
           set result =  CONCAT(result,CONCAT('\'',SUBSTRING(TmpBid, 1, i - 1),'\'\,'));
           SET TmpBid =  SUBSTRING(TmpBid, i + 1, LENGTH(TmpBid));
  END WHILE;
  IF(LENGTH(result) > 0)
      THEN SET result = SUBSTRING(result,1,LENGTH(result)-1);
  END IF;
  return result;
END// 
delimiter  ;

The function is working perfectly.

mysql> BusinessList( '21,22' )
BusinessList( '21,22' )
-----------------------
'21','22'

But the query using the function does not worked either. here is the query.

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (BusinessList(d.dealBusinessLocations)));

I have also tried using static value for function argumet, But no use

select * from dp_deals as d left join dp_business_locations as b on(b.businessLocID IN (BusinessList('21,22')));

It seems that there is some problem with using value returned from the function.

Answer 1

First, read this:

Is storing a comma separated list in a database column really that bad?
Yes, it is

Then, go and normalize your tables.

---

Now, if you really can't do otherwise, or until you normalize, use the FIND_IN_SET() function:

select * 
from dp_deals as d 
  left join dp_business_locations as b 
    on FIND_IN_SET(b.businessLocID, d.dealBusinessLocations)

Then, read that article again. If the query is slow or if you have other problems with this table, then you'll know why:

Is storing a comma separated list in a database column really that bad?
Yes, it is

Answer 2

Simple, use find_in_set() instead.

SELECT * 
FROM dp_deals as d 
LEFT JOIN dp_business_locations as b 
       ON (FIND_IN_SET(b.businessLocID,d.dealBusinessLocations) > 0); 

See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set

Note that if you drop CSV and get out off hell you can use a simple join like:

SELECT d.*, GROUP_CONCAT(b.dealBusinessLocation) as locations
FROM dp_deals as d 
LEFT JOIN dp_business_location as b 
       ON (d.dealBusinessLocation = b.businessLocID); 

Which will be much much faster and normalized as a bonus.

Answer 3

I think your problem is that IN() doesn't expect to get one string with lots of fields in it, but lots of fields.

With your function you are sending it this:

WHERE something IN ('\'21\',\'22\''); /* i.e. a single text containing "'21','22'" */

And not the expected

WHERE something IN ('21','22');