SQL - Filter group when data are same-CodePudding

I am in the middle of issue with this SQL. I have a table where I have several material parts. Material parts are included in multiple final products. Some of final products can be active and some inactive.

There could be two situations. When all the final products from one material are Active - I can use them for next analysis. When final products from one material does not have same status, or all have Inactive - I can not use them for next analysis.

Do you have any idea how to solve this issue?

Thank you very much for your help.enter image description here

CodePudding user response：

A bit of rudimental solution. Rather than having active/inactive you can use 1's and 0's. 0 for active and 1 for inactive. Then use a subquery where Sum(column) = 0

SELECT *
FROM YourTable
WHERE Material IN (
        SELECT Material 
        From YourTable 
        Group by Material
        Having SUM([ActiveInactive]) = 0
        )