I have two tables, and want to extract certain columns from the first, based on 'child' data from the second.
Table 1) Lets call it Projects
proj_id | Name --------+--------- 10 | Proj_1 20 | Proj_2 30 | Proj_3 40 | Proj_4
Table 2) Lets call this one tasks
proj_id | task_id | Status --------+---------+----------- 10 | 1 | Ready 10 | 2 | Cancelled 10 | 3 | Ready 20 | 1 | Complete 20 | 2 | Ready 30 | 1 | Ready 30 | 2 | Not Ready 30 | 3 | Complete 40 | 1 | Ready 40 | 2 | Ready
Want I want to do is find out which 'projects' have 'tasks' that are 'ready'.
The tricky part here is that it is OK if other tasks are Complete, but its not OK if they are anything other than complete or ready
So in other words the output should look like this:
Name | Status -------+-------- Proj_2 | Ready Proj_4 | Ready
What I don't want in the result set is to see Proj_1 (a task was cancelled) or Proj_3 (a task is not ready)
I'm not posting any SQL, because I'm not sure if this is even possible....
Normally I would do something like this in C++ in 2 multiple statements, but in this case I need it in a single statement, as I need to pass the data to a third party printing program.
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
group by t.proj_id
having sum(case when status = 'ready' then 1 else 0 end) > 0 and
sum(case when status not in ('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.