mysqlsubqueryin-subquery

query to display a separate row for every value unexpectedly yields only the first value from the IN clause subquery

The select query has to display a separate row for every value but on execution it yields only the first value from the subquery in the 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-separated values.

How do I resolve 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 ...