[SOLVED] Exclude rows if a column has different values for a combination of other columns

Issue

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.

Solution

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

Answered By – Derrick Moeller

Answer Checked By – Katrina (BugsFixing Volunteer)