Is there a way to make an AND operation over a column of TINYINT(1) in MYSQL?-CodePudding

I got the following table and I need to return 1 if all rows have disponibilidad = 1

The following QUERY works just fine, but i was looking for a more efficient way of doing it.

QUERY:

SELECT IF(AVG(disponibilidad) < 1, 0, 1) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1;

RESULT:

As I see it, with an 'AND' it would be far more efficient, since it wouldn't have to do AVG().

CodePudding user response：

MySql does not support a boolean AND aggregate function like Postgresql's bool_and.

Why not a simple MIN():

SELECT MIN(disponibilidad) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1;

This will return 1 only if all values of the column are 1 (provided the column is not nullable) and 0 if there is at least one row with 0.

CodePudding user response：

How about something like

SELECT IF(COUNT(*)>0,0,1) AS newResult
FROM pasteleria.compone
RIGHT JOIN pasteleria.ingredientes
    ON pasteleria.compone.id_ingrediente = pasteleria.ingredientes.id_ingrediente
WHERE id_componente = 1
AND disponibilidad <> 1

so that if there are any rows where disponibilidad is not 1, you output 0, otherwise if it's zero (so all disponibilidad values are 1) you output 1?