I have following table created from series of SQL queries and now I need to retrieve records if at least one row in the group matches a condition.
For example I wan't to retrieve all group records where at least one of device's state is FAILED. In the example I provided here, except for MERCHANT_ID c0030-10155511 (where both devices are in success state), I wan't to retrieve other records where at least one device is failed along with it's success device. For example, both records of MERCHANT_ID c0040-10196477 , need to fetch since only one device is failed.
how can we achieve this in MySQL.
SELECT * FROM yourTable WHERE merchant_id IN ( SELECT merchant_id FROM yourTable WHERE state LIKE '%FAILED')