I have a session table like (table1):
| session ID | sender ID | event date |
|---|---|---|
| s1 | s1 | 2020-10-02 |
| s2 | s1 | 2020-10-06 |
| s3 | s2 | 2020-03-01 |
| s4 | s2 | 2020-03-02 |
| s5 | s3 | 2020-08-02 |
| s6 | s4 | 2020-02-02 |
And a transactions table (table2) like:
| transaction ID | sender ID | send date |
|---|---|---|
| t1 | s1 | 2020-10-01 |
| t2 | s1 | 2020-10-05 |
| t3 | s2 | 2020-04-01 |
| t4 | s3 | 2020-07-02 |
| t5 | s4 | 2020-12-12 |
I want to generate a table that only contains the sessions of the users who have not made any transaction before their session date. According to the above tables, I want to return:
| session ID | sender ID | event date |
|---|---|---|
| s3 | s2 | 2020-03-01 |
| s4 | s2 | 2020-03-02 |
| s6 | s4 | 2020-02-02 |
I want to solve this using Anti-Join. Would the below code work for this purpose?
SELECT *
FROM table1 t1
LEFT JOIN table2 t2
ON t2.sender_id = t1.sender_id
AND t2.event_date > t1.event_date
WHERE t2.sender_id IS NULL
Please feel free to suggest any method other than anti-join. Thanks!
CodePudding user response：
You can use EXISTS as in:
select *
from t1
where not exists (
select 1
from t2
where t2.sender_id = t1.sender_id and t2.send_date < t1.event_date
)