1

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
0

(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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.