Mysql IN query with sub condition

I have two tables named cities and cfgval If the siteid condition is true then my queries second part

SELECT cfgval FROM sitecfg where cfg='affiliate_sitecityidlock' and siteid=24

will return "18,20,12,15,22,4,3,9,"

with a trailing comma, I have removed this by using

SELECT TRIM(TRAILING ',' FROM query. But when I am putting all queries together it just return only one row, my query is shown below

select * from cities  where siteid=0 and id 
IN(
   SELECT TRIM(TRAILING ',' FROM
     (SELECT cfgval FROM sitecfg where cfg='affiliate_sitecityidlock' 
       and siteid=24)
     )
  )

If I am substitute with direct values ie, select * from cities where siteid=0 and id IN(18,20,12,15,22,4,3,9) it will return al开发者_如何转开发l rows , i am stuck with this problem

If cfgval returns a single row with a comma delimited column, you can use the FIND_IN_SET() function:

SELECT * 
FROM cities
WHERE siteid=0 
  AND FIND_IN_SET(id, (
   SELECT SUBSTRING(cfgval, 2)
   FROM sitecfg 
   WHERE cfg='affiliate_sitecityidlock' 
       AND siteid=24
    )
  )

Note: Since this is using a function, indexes cannot be used, you should think about storing the cfgval in multiple rows.

I think I know what your problem is: you think that the sub select will return a comma delimited list of ids and you have to trim the last comma... No. Try this:

select * from cities
where siteid=0 
and id IN (SELECT cfgval FROM sitecfg where cfg='affiliate_sitecityidlock' and siteid=24)

As I understand your query should look like this

SELECT * FROM cities WHERE siteid=0 AND LOCATE(CONCAT(',', id, ','),
CONCAT(',', (SELECT cfgval FROM sitecfg WHERE cfg='affiliate_sitecityidlock' AND siteid=24))