CODEWITHSUNDEEP
postgresql
Paritosh Piplewar
Paritosh Piplewar

Reputation: 8132

how to select only those brands which don't have status = true in any of the products

Query is 

select brand
from products
where status = false
and brand not in (
  select brand
  from products
  where status = true
  group by brand
)
group by brand;

Basically, i want to select only those brands which don't have status = true in any of the products.

I want to optimize the above query

Upvotes: 0

Views: 20

Answers (2)

klin
klin

Reputation: 121794

Use the boolean aggregate function bool_or():

select brand
from products
group by brand
having not bool_or(status);

Upvotes: 2

e_i_pi
e_i_pi

Reputation: 4820

You can convert the boolean values to bit using a CASE statement, then MAX on that:

SELECT brand
FROM products
GROUP BY brand
HAVING MAX(CASE WHEN status = TRUE THEN 1 ELSE 0 END) = 1

Upvotes: 1

Related Questions