Sql Subselect Filtering Based On Multiple Sub-rows

I have two tables, and want to extract certain columns from the first, based on 'child' data from the second. I'm using SQL Anywhere 12 Table 1) Lets call it Projects proj_id |

Solution 1:

There are several ways to approach this type of query. I like to use aggregation with a having clause, because it is quite flexible and all the logic goes in the having clause:

select t.proj_id
from tasks t
groupby t.proj_id
havingsum(casewhen status ='ready'then1else0end) >0andsum(casewhen status notin ('complete, 'ready') then 1 else 0 end) = 0;

Each condition in the having clause counts the number of tasks that have a particular condition. The first counts the number of ready tasks, and the > 0 says there is at least one. The second counts the number of non-ready, non-complete counts. The = 0 says there are none.

Solution 2:

NOT EXISTS solution, i.e. return a project if it has a ready task, and no task other than ready and complete:

select p.*
from Projects p
  join tasks t on p.proj_id = t.proj_id
where t.Status = 'ready'andnotexists (select * from tasks t2
                  where t2.proj_id = t.proj_id
                    and t2.Status NOT IN ('ready', 'Complete'))