SELECT * FROM `products` d
JOIN (select distinct product_id from cs_inventory
    WHERE (meta_key="product_image" AND meta_value!="")
AND
(meta_key="product_type" AND meta_value="Soap" )
And
user_id='6' LIMIT 10) t on d.product_id = t.product_id