0

I have two MySQL tables. 

test_settings 
id, interval_in_minutes, other_irrevelant_settings

test_results
id, test_settings_id(Foregin Key), datetime, value

I would like to run query every 1 minute and run tests which last result was later than interval_in_minutes for every individual test.

Sample db data

test_settings
id interval_in_minutes
1 10 
2 40
3 140
etc

So finally result of query will be id 3 if there was no result inserted for last 140 minutes (based on test_result datetime inserted after test).

SELECT ts.id
FROM test_settings ts
WHERE (SELECT tr.date
       FROM test_results tr
       WHERE tr.test_settings_id = tr.id
       ORDER BY tr.datetime DESC
       LIMIT 1) < DATE_SUB(NOW(), INTERVAL XXXXX MINUTES);

It works only if interval is like XXXXX = 4 but I need it to be dynamic like XXXXX = ts.interval_in_minutes.

No idea how to do it.. Any help?

nomysz
  • 187
  • 1
  • 9
  • simply declare variable and use that – Hitesh Anshani Jun 21 '18 at 20:47
  • Please have look in this @nomysz https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – Hitesh Anshani Jun 21 '18 at 20:48
  • @D-johnAnshani thanks for reply, I tried few combinations but everytime sql syntax error near INTERVAL (like `INTERVAL @interv MINUTES`). Can you suggest usage in query? – nomysz Jun 21 '18 at 22:18
  • When you are using store procedure at that time its more usable sp work like a function you can pass values in parameter and then it will used as dynamically @nomysz – Hitesh Anshani Jun 22 '18 at 04:28
  • Ok I found simpler way by implementing conditions in code (maybe slightly worse in performance). – nomysz Jun 22 '18 at 21:51
  • Ok so your problem is now resolve or still pending?? – Hitesh Anshani Jun 23 '18 at 15:14
  • I dont know how to use it. Tried few combinations with no luck. I resolved it by changing logic so now query is simple but app has to do some work instead of db engine. My problem is resolved but my question not. – nomysz Jul 05 '18 at 20:27
  • I need to check more deeply maybe with some other resource – Hitesh Anshani Jul 05 '18 at 20:31

0 Answers0