CODEWITHSUNDEEP
sql
Fin
Fin

Reputation: 33

Get result true if all the rows have met the condition in sql

Basically i'm looking for solution for the condition, where table has the following rows and i want to select only those where condition is met by all the rows.

ID  category    flag
1       A       1
2       A       1
3       A       0
4       B       1
5       C       0

Expected Result is B where flag is true for its category.

Upvotes: 2

Views: 4927

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If you have a separate table of categories, then not exists is often the fastest method:

select c.*
from categories c
where not exists (select 1
                  from t
                  where t.category = c.category and 
                        t.flag = 0
                 );

In particular, this can take advantage of an index on (category, flag). If you don't have such a table, then forpas's solution is quite effective.

Upvotes: 0

forpas
forpas

Reputation: 164099

You want the categories for which the minimum flag is 1 (meaning there is no flag = 0):

select category
from tablename
group by category
having min(flag) = 1

See the demo.
Results:

| category |
| -------- |
| B        |

Upvotes: 3

JoPapou13
JoPapou13

Reputation: 773

I hope that my answer would be helpful to your problem. In the subquery, a list of the categories is created by filtering the flags.

SELECT * 
FROM tablename a 
WHERE a.category NOT IN (
      SELECT b.category
      FROM tablename  b 
      WHERE b.flag=0)

Upvotes: 3

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try this

select distinct category,flag from test where category in
(select t.category from (select category,flag from test 
group by category,flag)t group by t.category having count(*)=1) 
and flag=1;

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Use correlated subquery with not exists

select * from tablename a 
where not exists (select 1 from tablename b where a.category=b.cateogry and flag=0)

Upvotes: 0

Related Questions