Skip to main content
added 647 characters in body
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

"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 ;

You need to move the AND user_task.task_id= ? condition from the WHERE clause to the related ON:

SELECT User.* 
FROM User 
  INNER JOIN user_workspace 
    ON  user_workspace.user_id = User._id 
  LEFT OUTER JOIN user_task 
    ON  user_task.task_id = Task._id
    AND user_task.task_id= ?                <-- moved here
WHERE user_workspace.workspace_id= ?
  AND user_task.user_id IS NULL
ORDER BY User.name ASC ;

"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.user_id = User._id
    AND user_task.task_id = ?                <-- moved here
WHERE user_workspace.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 ;
Source Link
ypercubeᵀᴹ
  • 98.7k
  • 13
  • 215
  • 305

You need to move the AND user_task.task_id= ? condition from the WHERE clause to the related ON:

SELECT User.* 
FROM User 
  INNER JOIN user_workspace 
    ON  user_workspace.user_id = User._id 
  LEFT OUTER JOIN user_task 
    ON  user_task.task_id = Task._id
    AND user_task.task_id= ?                <-- moved here
WHERE user_workspace.workspace_id= ?
  AND user_task.user_id IS NULL
ORDER BY User.name ASC ;