1:M relation exclude row if any row in M table does not contain specific value Consider I have following table products

group   ads status
X       ad1 ACTIVE
X       ad2 NOT_BUYABLE
X       ad3 PAUSED
Y       ad4 ACTIVE
Y       ad5 SUSPENDED
Y       ad6 PAUSED
Z       ad7 ACTIVE
Z       ad8 OUT_OF_BUDGET
Z       ad9 ARCHIVED


All I want is to filter distinct group 

For example, I want to filter the group where


the product status != NOT_BUYABLE 
AND the product status = ACTIVE


consider Group X:


ad1 = ACTIVE 
ad2 = NOT_BUYABLE
ad3 = PAUSED


This filter would ignore Group X

consider Group Y:


ad4 = ACTIVE
ad5 = SUSPENDED
ad6 = PAUSED


This filter would return Group Y

consider Group Z:


ad7 = ACTIVE
ad8 = OUT_OF_BUDGET
ad9 = ARCHIVED


This filter would return Group Z

so final output should be 

group
Y
Z


This means the user only wants to filter product that doesn't have any NOT_BUYABLE status values but has at least 1 ACTIVE status.

when I apply != operator I have to make sure none of the product under that group has those staus

This SQL query is wrong

Select  distinct `group`   from products
where status != 'NOT_BUYABLE' and status = 'ACTIVE';


Let me know what will be the SQL query
Plan A:

SELECT a.group
    FROM ( SELECT DISTINCT `group` FROM tbl ) AS a
    WHERE
         EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'ACTIVE' )
     AND
     NOT EXISTS ( SELECT * FROM tbl WHERE group = a.group
                                      AND status = 'NOT_BUYABLE' )


Plan B:

SELECT a.group,
       GROUP_CONCAT(status) AS stati
    FROM tbl AS a
    GROUP BY `group`
    HAVING      FIND_IN_SET('ACTIVE',      stati)
        AND NOT FIND_IN_SET('NOT_BUYABLE', stati)


(To get rid of the second column, you need to wrap that in another SELECT.)

(Suggest changing your column name away from the keyword "group".)

Recommend this for both queries:

 INDEX(`group`, `status`)


(I don't know which Plan will be faster.)