CODEWITHSUNDEEP
mysqlsqljoin
KCore
KCore

Reputation: 295

mysql join query that needs to return negative set

Suppose i have 2 tables for user & task. have user_id & status in task with status having possible values "complete" & "not complete". Now I want to retrieve users who have not completed even 1 task. The most crude way is to first find users who have atleast 1 complete task and run a "not in" query.

Any better ways to achieve this without an "in" subquery. Please note that the data set is huge and i can't afford to have a lock on the task table for long time!

Upvotes: 1

Views: 231

Answers (3)

Chris Bandy
Chris Bandy

Reputation: 1608

When task.user_id cannot contain NULL (i.e. has a NOT NULL constraint), LEFT JOIN with IS NULL is your best choice:

SELECT user.* FROM user
LEFT JOIN task ON (task.user_id = user.id AND task.status = 'complete')
WHERE task.user_id IS NULL

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Try this one below query will list the users that have completed even 1 task or more than 1 

SELECT u.*,
COUNT(  CASE WHEN  t.`status`= 'Completed' THEN t.`status` END  ) AS completed ,

COUNT(  CASE WHEN  t.`status`= 'Not completed' THEN t.`status` END  ) AS Not_completed

FROM `user`  u

LEFT JOIN `task` t  ON (u.id =t.user_id)

GROUP BY t.user_id HAVING completed >0

And this will list the users those who haven't completed even 1 task

SELECT u.*,
COUNT(  CASE WHEN  t.`status`= 'Completed' THEN t.`status` END  ) AS completed ,

COUNT(  CASE WHEN  t.`status`= 'Not completed' THEN t.`status` END  ) AS Not_completed

FROM `user`  u

LEFT JOIN `task` t  ON (u.id =t.user_id)

GROUP BY t.user_id HAVING completed = 0

See fiddle for task completed users

See fiddle for users that have not completed task

Upvotes: 0

wildplasser
wildplasser

Reputation: 44240

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT * FROM tasks t
    WHERE t.user_id = u.user_id
    AND t.status = 'complete'
    );

Upvotes: 1

Related Questions