"Find all the Users of a particular workspace that are not associated with a specific task"
You need to move the AND user_task.task_id= ?
condition from the WHERE
clause to the related ON
.
(I think you also have a typo. ON user_task.task_id = Task._id
should be ON user_task.user_id = User._id
):
SELECT User.*
FROM User
INNER JOIN user_workspace
ON user_workspace.user_id = User._id
LEFT OUTER JOIN user_task
ON user_task.task_iduser_id = TaskUser._id
AND user_task.task_id=task_id = ? <-- moved here
WHERE user_workspace.workspace_id=workspace_id = ?
AND user_task.user_id IS NULL
ORDER BY User.name ASC ;
You could also use NOT EXISTS
. I find the code more readable:
SELECT User.*
FROM User
INNER JOIN user_workspace
ON user_workspace.user_id = User._id
WHERE user_workspace.workspace_id = ?
AND NOT EXISTS
( SELECT *
FROM user_task
WHERE user_task.user_id = User._id
AND user_task.task_id = ?
)
ORDER BY User.name ASC ;