开发者

MySQL: Return only results that lack specific criteria?

开发者 https://www.devze.com 2023-03-30 17:47 出处:网络
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.

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
0

精彩评论

暂无评论...
验证码 换一张
取 消