SQL subselect filtering based on multiple sub-rows

Question

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 | 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.

What I forgot to mention is that this statement actually has 6 tables to join (with future expansion possible) and the statement itself will be stored in the DB, which has a 2000 character limit.... — , Jan 14 '16 at 13:41

jarlh · Answer 1 · 2016-01-14T13:09:13.683

0

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'
  and not exists (select * from tasks t2
                  where t2.proj_id = t.proj_id
                    and t2.Status NOT IN ('ready', 'Complete'))

edited Jan 14 '16 at 13:09

answered Jan 14 '16 at 12:49

jarlh

42,561
8
45
63

max = min doesn't allow for 'Complete' which is allowed, maybe: `min(status) in ('Complete','Ready')` – Paul Maxwell Jan 14 '16 at 13:02
@Used_By_Already, sorry, didn't read carefully enough... Now corrected. – jarlh Jan 14 '16 at 13:08
This is very similar to what I initially tried to do, but unfortunately (there are 6 tables to join) the statement became very messy. Thanks for your answer. – Jan 14 '16 at 13:38

score 0 · Accepted Answer · answered Jan 14 '16 at 12:54

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.

This is what I need thank you! I can keep it tidy even if (when) I need to add to the statement later. — , Jan 14 '16 at 13:39
I can't believe I didn't think of using having, too close to the problem I guess. — , Jan 14 '16 at 13:39

SQL subselect filtering based on multiple sub-rows

2 Answers2

Linked