Get result where exist on date X and not exist on day Y-CodePudding

I have a table where I store some info about events. My table have columns: id, created_at(which is date field), key(which is varchar255), quantity(which is integer) and event(which is varchar255). I'm making query to take all keys which exists on date X (for example 2022-09-05) and NOT exists on date Y (example 2022-09-06). The table has no relation with other tables.

The query that I tried is:

SELECT s.key
FROM stats s
WHERE created_at = '2022-09-05'
AND NOT EXISTS(
    SELECT *
    FROM stats s
    WHERE s.created_at = '2022-09-06'
    )
GROUP BY s.key
;

The problem is this returns me 0 result, but I expect at least 1.

CodePudding user response：

You can try this

SELECT s.key
FROM stats s
LEFT JOIN (
    SELECT s.key FROM stats s
    WHERE created_at = '2022-09-05'
    ) dayAfter ON s.key = dayAfter.key
WHERE s.created_at = '2022-09-06'
AND dayAfter.key IS NULL
GROUP BY s.key

CodePudding user response：

You have to check that the key of 2022-09-05 does not appear on 2022-09-06. So the query changes to

SELECT s.key
FROM stats s
WHERE s.created_at = '2022-09-05' AND NOT EXISTS
 (SELECT FROM stats st WHERE st.key = s.key AND st.created_at = '2022-09-06');