Mysql – Need a help in Query to check whether Task is completed by other user or not before showing on dashboard

MySQL

I have 3 tables in database Task, User & assignedTo.

Task contains information of Task created by Admin, User is list of User & assignedTo contains information about which task assign to which user.

i need to query on 'assignedTo' table to show a list of Task assigned to users on their Dashboard. Problem is a single Task is assigned to multiple user and it may possible task may be completed by one of them. So i need to show task which are in Open Status and not completed by anyone in assigned list.
Below is structure of 'assignedTo' Table:

--------------------------------
| id | taskId | userId | status|
--------------------------------
| 1  |  1     | 1      | open  |  
| 2  |  1     | 2      | open  |
| 3  |  1     | 3      | open  |
| 4  |  1     | 4      | open  |
| 5  |  2     | 1      | open  |
| 6  |  2     | 2      | close |
| 7  |  3     | 1      | open  |
| 8  |  3     | 2      | open  |
--------------------------------

So as per above data, Task 1 is shown on user 1,2,3 & 4 Dashboard as it is not closed by any of them.
Task 2 is shown on no one's Dash as it is closed by User 2.
Similarly, Task 3 is shown on user 1 & 2 Dash as it is open for both of them.

i tried to join same table to it's own but failed to execute.

SELECT * FROM assignedTo  a1
JOIN assignedTo a2 ON a2.taskId = a1.taskId AND a2.userId != 1 AND a2.status = 'open' 
WHERE a1.userId = 1 AND a1.status = 'open'

Please help with Query if possible.

Best Answer

SELECT  A1.*, 
        CASE WHEN A2.taskId IS NULL THEN 'Task Completed' 
        ELSE 'open' END AS CurrentStatus
FROM AssignedTo A1
LEFT JOIN (
    SELECT taskId
    FROM AssignedTo 
    WHERE status IN ('open', 'close')
    GROUP BY taskId
    HAVING COUNT(DISTINCT status) = 1
    ) A2 ON A2.taskId = A1.taskId
ORDER BY A1.taskId;

Fiddler: http://rextester.com/CAHOPS39734

Help from this post

The above query will return one extra column as CurrentStatus. If the taskId has any close status, for those tasks the CurrentStatus will be Task Completed and for remaining tasks it will show as open only.

So the output will be:

id  taskId  userId  status  CurrentStatus
1   1       1       open    open
4   1       4       open    open
2   1       2       open    open
3   1       3       open    open
5   2       1       open    Task Completed
6   2       2       close   Task Completed
7   3       1       open    open
8   3       2       open    open
Related Question