CODEWITHSUNDEEP
mysqlsql
Haradzieniec
Haradzieniec

Reputation: 9340

MySQL: Select rows by the reverse criteria

The first query I wrote is:

select t.groupNum from table1 t
JOIN table1 s
on t.groupNum=s.groupNum
and t.id!=s.id 
and t.bool='true' and s.bool='true';

This query selects such groups that contain at least two 'true' within the group. Right?

I need to select all groups that have exactly zero 'true' values (only t.bool='false' which is the second possible value among two values 'true' and 'false').

Any help?

Upvotes: 1

Views: 144

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use group by and having to do this.

select groupNum
from table1
group by groupNum
having sum(bool='true') = 0

MySQL treats conditions as booleans returning 1 when the condition is True and 0 otherwise.

Upvotes: 3

bbrumm
bbrumm

Reputation: 1352

How about this:

SELECT t.groupNum
FROM table1 t
GROUP BY t.groupNum
HAVING COUNT(t.bool = 'true') = 0;

I'm not sure why you're doing the self join. Also is the t.bool column actually a text value with the values of 'true' and 'false'?

Upvotes: 1

Related Questions