2

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.

2
  • One small issue is, no need the double equal symbol here a2.status == 'open'
    – Arulkumar
    Commented Jun 20, 2016 at 7:43
  • @Arulkumar Question Updated, typing mistake... Commented Jun 20, 2016 at 9:13

2 Answers 2

2
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
1
  • Would not that also return tasks that are closed and assigned to one userId? You could change the having clause to HAVING COUNT( case status when 'close' then 1 end ) = 0 to handle this situation. Commented Jun 20, 2016 at 8:38
2

Slight variation of @Ankumars answer. Since 'close' is less than 'open':

SELECT  A1.*, A2.status as current_status
FROM AssignedTo A1
JOIN (
    SELECT taskID, min(status) as status
    FROM AssignedTo
    GROUP BY taskId
) A2
    ON A1.taskId = A2.taskId;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.