MySQL. Select if column of each row is TRUE?
I am querying two tables through a JOIN query to determine if each row is in the results column approved
TRUE
.
SELECT
`approvals`.`approved`
FROM `sign_ins`
RIGHT JOIN `approvals` ON
`sign_ins`.`user` = `approvals`.`user`;
This will return a resultant set of boolean values for each user like
1 0 0 1 1
My PHP code iterates over these lines and determines if everything is approved simply by returning false and breaking the loop if any of those values 0
. However, I think it would be more efficient and optimal if there was no repetition in the result set; at the end of the day, I really only care about one TRUE
thing : if all string values approved
TRUE
; the else, FALSE
.
Can I do this with a MySQL query?
Thank.
source to share
Here is my solution. I am using a function MIN
to select the smallest value, so it should return 0
if there are any values FALSE
. In addition, since it is not a complete join, a feature that is missing from the table approvals
should be considered unapproved; so I used IFNULL
to provide the default FALSE
.
Here is a complete example of my query (minus some kind of date logic):
SELECT
MIN(IFNULL(`approvals`.`approved`, FALSE))
FROM `sign_ins`
LEFT JOIN `approvals` ON
`sign_ins`.`user` = `approvals`.`user`;
source to share
I would just take COUNT()
all rows and compare this with the SUM()
approved column. If they match, then all strings are 1. If they don't, then there must be at least one 0.
Usage COUNT(approved)
won't count the values NULL
, so you don't need to compensate for that.
Your request will look like this:
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.
source to share