I'm trying to construct a MySQL query that will only return unassigned tasks. A task i开发者_运维百科s considered assigned when it has one or more assignments
where is_assignee
equals y
.
tasks
- id
- name
- description
assignments
- task_id
- user_id
- is_assignee
- is_owner
I made the mistake of previously finding tasks that simply had no related assignments
rows:
SELECT t.*
FROM tasks t
LEFT OUTER JOIN assignments a
ON t.id = a.task_id
WHERE a.id is NULL
That worked great, but only returned tasks that had no owner AND no assignee. What I need is to return tasks that have no related assignments
rows with is_assignee
set to 'y'. Is there an efficient way to return only the unassigned task
rows I'm looking for?
I may be misunderstanding, but adding additional logic to your JOIN
clause to filter only where is_assignee = 'y'
should do it.
SELECT t.*
FROM tasks t
LEFT OUTER JOIN assignments a
ON t.id = a.task_id AND a.is_assignee = 'y'
WHERE a.id is NULL
精彩评论