Question 113612 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Return the data based on the statuses from other table in SQL

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!

Tags:

2

Answers


  1. 0 
        select u.id , u.name,t.status from users u 
left join tasks t on t.user_id = u.id
where t.status<>'new';
    Login or Signup to reply.
  2. 0

    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

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search