SQL query to filter records based on absence of values in a group

I've racked my brains and googled extensively to find a solution and I suspect I may not be asking the question clearly so please bear with me.

I've got to build a couple of queries that filter records on the following basis. Although multiple tables involved in extracting the data I'll stick the basic requirement.

The following are the sample values:

Key | Decision   
123 |  Complete  
123 | Additional info  
123 | Something  
123 | Complete
.  
.  
.  
254 | Complete  
254 | Complete  
254 | Complete  
.  
.  
.  

Based on the above data I can do a select and group by Key and Decision to get data set as follows:

Key | Decision  
123 | Complete  
123 | Additional info  
123 | Something  
.  
.  
.  
254 | Complete   
.  
.  
.

The actual data I need is of two types (these are separe queries that have to be built)

1) Keys where the only decision is "Complete" - In the above example only Key=254 would match

2) Keys where decision could contain "Additional info" - In the above example only Key=123 would match

It seems almost possible, like I have the answer floating around somewhere, and I can't quite grasp it. Or is this wishful thinking?

I did try the following

select key from table where decision not in (select key from table where decision <> "Complete") 

This gets me the result I want for Decision=Complete. However, with the final selection being at least containing at least three joins, I suspect that the performance is going to be bad. The queries will be executed on Oracle 11g.

If anyone has suggestions that helps me get out of this ideas rut, I wo开发者_运维技巧uld deeply appreciate it.

For the first question

select `key` from your_table
group by key
having count(decision) = sum(decision="complete")

for the second one

select `key` from your_table
where decision = 'Additional Info'
group by `key`

1) Keys where the only decision is "Complete" - In the above example only Key=254 would match

select key
  from table
 group 
    by key
having min(decision) = 'Complete'
   and max(decision) = 'Complete'

or what @nick rulez wrote with the following modification (to make it run on Oracle as well):

having count(decision) = sum(case when decision = 'Complete' then 1 else 0 end)

2) Keys where decision could contain "Additional info" - In the above example only Key=123 would match

select distinct key 
  from table
 where decision = 'Additional info';