Ragith Thomas Ragith Thomas - 27 days ago 12
MySQL Question

sql query to list all rows if column date with current date exceeds 24 hrs

Thanks in advance,I am using Mysql database. Actually i need a row of data whose order_date column with datetime datatype exceeds 24 hrs when comparing with current date and time. Other table with name "checks" contains same orderid compared with carts table orderid.
so i need a sql query to get a row of carts table data when comparing carts order_date with current date and carts table with orderid same as checks table orderid should be neglected.

carts table:
S.No | username | orderid | exam_name | order_date |
1 | Rakesh |746G797W819d3343493z | PMP | 2016-11-05 11:34:5 |
2 | Rakesh |746G797W819d3343493z | CAPM | 2016-11-05 11:34:5 |
3 | Kulkarni |24890W819d6743493W | CAPM | 2016-11-05 11:34:5 |
4 | Suresh |52890W819d6743493W | PMP | 2016-11-05 11:34:5 |
5 | Gautham |90890W819d6743493W | PMP | 2016-11-05 11:34:5 |

checks table:
S.No | username | orderid | exam_name | checked_date
1 | Rakesh |746G797W819d3343493z | PMP | 2016-11-07 11:20:5
2 | Kulkarni |24890W819d6743493W | CAPM | 2016-11-07 11:20:5


Remaining rows as below is needed

S.No | username | orderid | exam_name | order_date |
1 | Rakesh |746G797W819d3343493z | CAPM | 2016-11-05 11:34:5 |
2 | Suresh |52890W819d6743493W | PMP | 2016-11-05 11:34:5 |
3 | Gautham |90890W819d6743493W | PMP | 2016-11-05 11:34:5 |

Answer

I would do something like the following:

SELECT
    *
FROM
    carts
LEFT JOIN
    checks ON
    carts.orderid = checks.orderid AND
    carts.exam_name = checks.exam_name
WHERE 
    checks.orderid is null AND
    checks.exam_name is null AND
    carts.order_date < NOW() - INTERVAL 1 DAY