I want a list of user_id which shouldn't have zero status.

Lets say, I have task table with user id, status. I'm trying to write a query to fetch user ids which have status = 1 only but not 2. As for below table, it should get me users id of tables with only status =1;

User table

Tasks table:

I have tried this query

SELECT user_id FROM tasks where status =2 and status != 1;

The above user id 100 has two tasks one with status 1 and other with status 2, I don't want that user. The above user id 200 has two tasks but none of them has status 1, that is what i want. The above user id 300 has both tasks with status 1, I don't want it too.

Status 1 means open. So I want a query which should get me users with zero open tasks only. If it has status 1 and 2 both, I don't want that.

I have tried multiple queries, but unable to find it.

CodePudding user response：

I have task table with user id, status. I'm trying to write a query to fetch user ids which have status = 1 only but not 2

SELECT *
FROM users
WHERE EXISTS ( SELECT NULL
               FROM tasks
               WHERE users.id = tasks.user_id
                 AND statis IN (1)       -- must have 1
               )
  AND NOT EXISTS ( SElECT NULL
                   FROM tasks
                   WHERE users.id = tasks.user_id
                     AND statis IN (2)   -- must not have 2
                   )

To test multiple values put according list to according WHERE .. AND statis IN (..) list.

CodePudding user response：

Using sub queries with IN()/NOT IN() you can build a list of users having tasks in status 1/2 and filter your users based on those lists:

SELECT *
FROM users
WHERE
    -- ensure the user has at least 1 task in status 1
    id IN(
        SELECT user_id
        FROM tasks
        WHERE status = 1
    )
    -- ensure the user has no task in status 2
    AND id NOT IN(
        SELECT user_id
        FROM tasks
        WHERE status = 2
    )