Search code examples
mysqlsubqueryin-subquery

Below query doesnt give me expected result please help me out

The select query has to display seperate row for every value it gets but on executing this query it yields only first value from the sub query used in IN clause.

    SELECT prod_id FROM tbl_product WHERE tbl_product.prod_status = 1 AND 
tbl_product.is_excluded = 0 AND tbl_product.prod_stock_qty > 0 
AND tbl_product.prod_id IN (SELECT rel_prod_ids 
FROM tbl_product_relations WHERE prod_id = '6058')

The subquery yields the Comma seperated values

Could someone help me out to find me the resolution of this?

Solution

  • What you need is find_in_set

    select find_in_set(123, '123,12345,123456');  <-- return 1
select find_in_set(123, '1234,12345,123456'); <-- return 0

    This function is quite slow and beware over the performance ...