I have this dataset :
UserPlans table :
+----+------+-------+-----------------------+ | ID | userId | name | deletedAt | +----+--------+-------+---------------------| | 1 | 1 | plan1 | 2020-07-30 13:41:50 | +----+--------+-------+---------------------| | 2 | 1 | plan3 | NULL | +----+--------+-------+---------------------| | 3 | 2 | plan2 |2020-07-30 15:30:10 | +----+--------+-------+---------------------|
how to only select this row (with ID 3) since this data has been deleted but doesn’t have any data with deletedAt = NULL
with same userId
?
+----+--------+-------+---------------------| | 3 | 2 | plan2 |2020-07-30 15:30:10 | +----+--------+-------+---------------------|
Advertisement
Answer
You seem to want rows where userid
has no other row whose deletedAt
is null
. If so, you can use not exists
and a correlated subquery:
select t.* from mytable t where not exists ( select 1 from mytable t1 where t1.userid = t.userid and t1.deletedAt is null )