CoolCK CoolCK - 2 months ago 6
MySQL Question

Retrieve all the group records if at least one row matches the condition in MySQL

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.
enter image description here

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.

Answer

Try this

SELECT
    *
FROM
    yourTable
WHERE
    merchant_id IN
    (
        SELECT
            merchant_id
        FROM
            yourTable
        WHERE
            state LIKE '%FAILED')
Comments