Question: 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!

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

```
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**](http://sqlfiddle.com/#!2/eb24d/2)

[**See fiddle for users that have not completed task**](http://sqlfiddle.com/#!2/eb24d/3)