MySQL Help - Combining 2 MySQL Selects - Result in 1

Question

I have two MySQL statements (see below), I would like to combine them together so if they both result in a 1 then the end result will be 1. I'm not sure how to construct this and was hoping for some help.

select count(*)
from monitor
where name='job_starttime' and value < ( UNIX_TIMESTAMP( ) -600)
select count(*)
from monitor
where name='job_active' and value = 0

So for example I would like when both statements are true to result in a value of 1, if 1 or none are true it results in a 0.

I nearly achieved it with the below SQL (except it's giving a result for each entry in the table) :(

select
case
when (select count(*) from monitor
where exists (select * from monitor where name = 'job1_active' AND value = 1)
and exists (select * from monitor where name = 'job2_active' AND value = 1)) > 1
then 1
else 0
end
from monitor

Answer 1

(I am assuming you want the result in a WHERE or some other place for testing?)

This is faster, but is equivalent to COUNT(*)>= 1.

SELECT ...
 WHERE EXISTS ( SELECT * FROM monitor
 WHERE name = 'job_starttime' AND value < ... )
 AND EXISTS ( SELECT * FROM monitor
 WHERE name = 'job_active' AND value = 0 )

Otherwise:

SELECT ...
 WHERE ( SELECT COUNT(*) ... ) = 1
 AND ( SELECT COUNT(*) ... ) = 1

Another tip: false == 0; true == 1.

• daygle: @ Rick James - Sorry I'm just not getting it where you mention 'Select ...' what should I replace that with?
• Rick James: How about SELECT 1 - you will get either 1 or NULL.
• daygle: So - SELECT 1 WHERE ( SELECT COUNT() ... ) = 1 AND ( SELECT COUNT() ... ) = 1
• Rick James: See if it works for you.
• daygle: Unfortunately not :(, I didn't think it would be this hard to combine these select statements but it is proving difficult.