Ron van der Heijden Ron van der Heijden -4 years ago 68
MySQL Question

Get cell value in MySql query where date

I have the following table:

-----------------------------
| id | deadline | Notify |
-----------------------------
| 1 | 2017-06-16 | 4 | // true
| 2 | 2017-06-17 | 5 | // true
| 3 | 2017-06-18 | 5 | // false -> tomorrow
| 4 | 2017-06-19 | 6 | // false -> tomorrow
-----------------------------


The notify column present the days before the deadline when a notification should be sent.

Using the following query gives me the record for all the deadlines in 5 days

SELECT * FROM `issues` WHERE deadline = DATE(NOW() + INTERVAL 5 DAY)


How can I use the cell information of the same row? So that, in this example, I get the rows #1 and #2?

Answer Source

Try this:

SELECT *
FROM issues
WHERE deadline - INTERVAL Notify DAY <= NOW() 

The above query returns rows 1, 2. If you want to return all rows with a true, false flag then you can try the following query:

SELECT *,
       IF(deadline - INTERVAL Notify DAY <= NOW(), true, false) AS flag
FROM issues

Demo here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download