Select items in table A that didn't have any new records in table B

Within table B I have a column created_at.

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

1 answer

  • answered 2019-03-13 19:34 DanB

    You want missing record, you cannot use INNER JOIN because 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;