This is my table:
x
project_id | task_id | task_name | task_status |
---------+---------+-----------+-------------+
1 12 foo complete
2 13 foo complete
3 1210 bar complete
4 1211 bar none
5 1212 xyz none
6 1213 zyz none
I want create query where I can select only tasks_name
where task_status
“complete” for both task_id
. For example one task which name foo
have two task_id
such as 12 and 13 and both of them have task_status
complete. In contrast task with name bar
have only one task_id
“completed”, so its fall for my condition.
I expect get table like this:
project_id | task_id | task_name | task_status |
---------+---------+-----------+-------------+
1 12 foo complete
2 13 foo complete
How I can select, check and return where task_name
is same for each task_id
and both of them have task_status
completed
Advertisement
Answer
It is much easier to do this using min, max and a subquery.
Select *
From Tbl
Where task_name In (Select task_name
From Tbl
Group by task_name
Having Min(task_status)=Max(task_status)
And Max(task_status)='complete')
Data output:
project_id | task_id | task_name | task_status
___________|_________|___________|____________
1 | 12 | foo | complete
2 | 13 | foo | complete