The goal is to return an array of available users that are assigned to the site we are working on and are available to do the task we want to assign.
For this question, we can assume that we are working on site_id 4 and we are trying to assign task_id 2.
site_tasks_assignments
id | user_id | site_id | task_id | complete
--------------------------------------------
12 | 6 | 4 | 2 | 1
13 | 4 | 3 | 2 | 0
14 | 2 | 4 | 3 | 0
15 | 6 | 4 | 4 | 1
users
id | name | site_id
-----------------------
1 | bob | 1
2 | henry | 4
3 | jane | 2
4 | mary | 3
5 | kyle | 4
6 | steve | 4
Users should show up in the results if:
- The
users.site_id
is = the id of the site we’re working at (again, assumesite_id
4)
AND, any of the following are true:
- the
users.id
does not appear in thesite_task_assignments.user_id
at all, meaning they have been assigned no tasks at any sites, - the user is in the
site_task_assignments
table but has not been assigned that specific task (task_id
2, in this case), - the user is in the
site_task_assignments
table and has been assigned that specific task AND thesite_task_assignments.complete
column for that row is 1
I expect:
id | name | site_id
-----------------------
2 | henry | 4 <- not assigned to task 2, can work on site 4
5 | kyle | 4 <- not assigned to any tasks, can work on site 4
6 | steve | 4 <- WAS assigned to task 2, task 2 complete, can do task 2 again, can work on site 4
I tried several versions of a query, some involving some sort of JOIN, and a couple with subqueries, etc. The closest I’ve gotten:
SELECT u.*, sta.task_id, sta.user_id, sta.complete
FROM users AS u
LEFT JOIN site_task_assignments AS ci
ON u.id = sta.user_id
WHERE u.site_id = 4
AND (u.id NOT IN (SELECT user_id FROM site_task_assignments)
OR (u.id IN (SELECT user_id FROM site_task_assignments)
AND sta.task_id = 2 AND complete = 1)
OR (u.id IN (SELECT user_id FROM site_task_assignments)
AND (sta.task_id = 2 AND complete = 1
OR sta.task_id != 2 AND complete = 0)))
This gives me inconsistent results. On some tasks, it’s fine, on other tasks that have already been assigned it sometimes gives me users where they have already been assigned to a task but complete is 0. On tasks that have not been assigned at all, it gives me duplicates of the same user if they are already in the site_task_assignments
table for other tasks.
2
Answers
Try this:
See https://www.db-fiddle.com/f/iB4hSEHQwNspZ3Md9tmW69/0
I might be missing something but it seems that your three criteria can be rewritten as:
users.id
is not currently assigned to incompletetask_id
2Here’s a db<>fiddle.