c1  |   c2  |  c3
----|-------|----
A   |   Z   |  false
A   |   Z   |  true
P   |   Y   |  false
Q   |   X   |  true


Output
---------------
P   |   Y   |  false

For the given table above, I'm trying to write a sql query that meets the below conditions :

• If for a combination of c1 and c2, c3 has both false and true values - ignore those rows.
• Also ignore those rows whose c3 value is only true, for a combination of c1 and c2
• Return those rows whose combination of c1 and c2 has the only value false in c3

What I tried :

To solve this problem, I tried looking at self-join and tried using intersect / except operators but that didn't help in any form.

CodePudding user response：

You can do this with a combination of GROUP BY and CAST. First you can look for c1 and c2 combinations that occur only once, then you can filter for combinations that have a c3 of false.

SELECT c1, c2, MIN(CAST(c3 AS INT)) AS c3
FROM YourTable
GROUP BY c1, c2
HAVING COUNT(DISTINCT c3) = 1 AND MIN(CAST(c3 AS INT)) = 0

CodePudding user response：

In MySQL, boolean is just tinyint(1) and true and false are just 1 and 0. You can group by c1, c2 and check that the sum of c3 is 0.

select c1, c2, sum(c3)
from test
group by c1, c2
having sum(c3) = 0

Demonstration