I have table with users:
+-----------+----------+
| id | name |
+-----------+----------+
| 1 | Joe |
| 2 | Tom |
| 3 | Jack |
| 4 | Tim |
+-----------+----------+
and second table with tasks liked with these users:
+--------------+--------------+--------------+
| id | user_id | status |
+--------------+--------------+--------------+
| 1 | 1 | new |
| 2 | 1 | done |
| 3 | 1 | in_progress |
| 4 | 2 | in_progress |
| 5 | 2 | done |
| 6 | 2 | done |
| 7 | 2 | done |
| 8 | 3 | new |
| 9 | 3 | new |
| 10 | 3 | new |
| 11 | 4 | in_progress |
| 12 | 4 | in_progress |
| 13 | 4 | new |
+--------------+--------------+--------------+
Each task could be in 'new', 'in_progress' or 'done' status.
I would like to get a list of user_ids who do not have any tasks in 'new' status but have a task in 'done' status.
Could anyone help me with this? Thanks in advance!
A variety of ways to accomplish this. Here are just a couple:
Query #1: Use CTEs
with done as (
select distinct user_id
from tasks
where status = 'done'
),
new as (
select distinct user_id
from tasks
where status = 'new'
)
select u.id, u.name
from users u
join done d
on u.id = d.user_id
where u.id not in (select user_id from new);
| id | name |
|---|---|
| 2 | tom |
Query #2: No CTEs
select id, name
from users
where id in (select user_id from tasks where status = 'done')
and id not in (select user_id from tasks where status = 'new');
| id | name |
|---|---|
| 2 | tom |