Select items in table A that didn't have any new records in table B
table B I have a column
I want to select all rows from
table A that didn't have a new row created within
table B in the last 1-2 minutes from the last entry.
If I run query on the table below at 18:01:01, the result should only include tableA_id with ID 1.
Table B id, tableA_id createdAt 1, 1, 18:00:00 2, 1, 17:59:00 3, 2, 17:59:00 4, 3, 18:01:00
My current query looks similar to the query below:
SELECT tableA.* FROM tableA INNER JOIN tableB ON tableB.tableA_id = tableA.id WHERE tableB.created_at NOT BETWEEN NOW() AND NOW() - INTERVAL 1 MINUTE AND tableB.created_at BETWEEN NOW() - INTERVAL 1 MINUTE AND NOW() - INTERVAL 2 MINUTE
missingrecord, you cannot use
INNER JOINbecause this will output only existing record.
I'm not sure which interval you want. Try this for no record from 1 minute in past to now.
SELECT tableA.* FROM tableA LEFT JOIN tableB ON tableB.tableA_id = tableA.id AND tableB.createdAt between now() interval -1 minute and now() where tableB.tableA_id is null;