Please help me, I have a table like below and I want to get data by id with status 'sent' and ' ' only and not with status 'delivered'.
| message_thread_id | status |
|---|---|
| 229 | |
| 229 | delivered |
| 229 | sent |
| 229 | delivered |
| 229 | delivered |
| 240 | sent |
| 240 | sent |
| 1044 | |
| 1044 | |
| 1044 | |
| 1068 | delivered |
CodePudding user response：
use NOT EXISTS
SELECT * FROM mytable a WHERE NOT EXISTS(
SELECT 1 FROM mytable b
WHERE a.message_thread_id = b.message_thread_id
AND status = 'delivered'
)