Below query doesnt give me expected result please help me out

Question

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?

Answer 1

Use a JOIN instead of a IN

SELECT p.prod_id
FROM tbl_product p JOIN tbl_product_relations pr ON p.prod_id = pr.rel_prod_ids
WHERE p.prod_status = 1
    AND p.is_excluded = 0
    AND p.prod_stock_qty > 0
    AND r.prod_id = '6058'

Answer 2

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 ...