Skip to content
Advertisement

Mysql how to select only row in same table and same identifier but different value

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
)
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement