CODEWITHSUNDEEP
phpmysql
M Miller
M Miller

Reputation: 5642

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

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.

Upvotes: 6

Views: 3800

Answers (3)

wolfgangwalther
wolfgangwalther

Reputation: 1236

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.

Upvotes: 5

M Miller
M Miller

Reputation: 5642

Here was my solution. I use the MIN function to select the smallest value, so it should return 0 if there are any FALSE values present. Also, since it's not a full join, a sign in that is not present in the approvals table should be assumed to be unapproved; therefore, I used IFNULL to provide a default FALSE value.

Here's a complete example of my query (minus some date logic):

SELECT
    MIN(IFNULL(`approvals`.`approved`, FALSE))
FROM `sign_ins`
LEFT JOIN `approvals` ON
    `sign_ins`.`user` = `approvals`.`user`;

Upvotes: 1

kair
kair

Reputation: 1056

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

Upvotes: 2

Related Questions