CODEWITHSUNDEEP
mysqlsql
Zannix
Zannix

Reputation: 1593

How to select column values where all values of another column are equal to something?

I have a table with columns category_id and is_required (among others), and I wish to select all values of category_id where all records have is_required = 0.

Something like SELECT category_id FROM table WHERE ALL is_required = 0

Edit: Another way of phrasing it - I need to select only those category_id values which never have is_required = 1 in the entire table

Upvotes: 0

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can readily do this with not exists:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.category_id = t.category_id and
                        (t2.is_required <> 0 or t2.is_required is null)
                 );

If you only want the category ids, then aggregation might produce more reasonable results:

select category_id
from table t
group by category_id
having sum(t.is_required <> 0 or t.is_required is null) = 0;

Upvotes: 1

Jens
Jens

Reputation: 69440

Is that what you need?

select t1.category_id from `table` t1 left join `table` t2  on t1.category_id =t2.category_id and t1.is_required = 0 and t2.is_required = 1 where t2.category_id is null.

Upvotes: 0

Related Questions