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!

CodePudding user response：

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);

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');

View on DB Fiddle

CodePudding user response：

    select u.id , u.name,t.status from users u 
left join tasks t on t.user_id = u.id
where t.status<>'new';