This is my table:

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

CodePudding user response：

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   

CodePudding user response：

So that means that, per task_name, the number of completed need to be the same as the total number.

select project_id, task_id, task_name, task_status
from 
(
  select *
  , count(case when task_status = 'complete' then 1 end) 
     over (partition by task_name) as cnt_complete
  , count(*) 
     over (partition by task_name) as cnt
  from yourtable
) q
where cnt = cnt_complete

Demo on db<>fiddle here

CodePudding user response：

Using window functions to filter task names that are all complete.

select project_id, task_id, task_name, task_status from 
(
 select *,
  count(*) filter (where task_status = 'complete') over w = count(*) over w all_complete
  from the_table window w as (partition by task_name)
) t 
where all_complete; 

CodePudding user response：

One way, using analytic functions:

WITH cte AS (
    SELECT *, MIN(task_status) OVER (PARTITION BY task_name) min_task_status,
              MAX(task_status) OVER (PARTITION BY task_name) max_task_status
    FROM yourTable
)

SELECT project_id, task_id, task_name, task_status
FROM cte
WHERE min_task_status = max_task_status AND
      min_task_status = 'complete';