MySQL - Select if the column of every row is TRUE?

Question

I am querying two tables via a JOIN query to determine if every row in the result set's approved column is TRUE.

SELECT
    `approvals`.`approved`
FROM `sign_ins`
RIGHT JOIN `approvals` ON
    `sign_ins`.`user` = `approvals`.`user`;

This will return a result set of boolean values for each user, e.g.

1
0
0
1
1

My PHP code iterates through these rows and determines if all are approved simply by returning false and breaking a loop if any of these values are 0. However, I think it would be more performant and optimal if iterating through the result set wasn't necessary; after all, I really only care about one thing: TRUE if all the rows' approved value are TRUE; else, FALSE.

Can I do this with a MySQL query?

Thanks.

Answer 1

I would just COUNT() all rows and compare that to the SUM() of the approved column. If they match, then all rows were 1. If they don't, then there was at least one 0.

Using COUNT(approved) will not count NULL values, so you don't need to compensate for that.

Your query would look like:

SELECT (CASE WHEN COUNT(approved)=SUM(approved) THEN 1 ELSE 0 END)
FROM sign_ins
RIGHT JOIN approvals USING (user)

This should return 1 for TRUE and 0 for FALSE.

Answer 2

If you just want all TRUE results, ask for it!

SELECT
    `approvals`.`approved`
FROM `sign_ins`
RIGHT JOIN `approvals` ON
    `sign_ins`.`user` = `approvals`.`user`;
WHERE `approvals`.`approved` = 1