Querying a subset

I want to write an SQL query to find records which contain a particular column and from that subset want to find records which doesn’t contain a some other value. How do you write a query for that?

cid  id2  attribute
--------------------------------
 1  100  delete 
 1  100  payment
 1  100  void
 2  100  delete
 2  102  payment
 2  102  void
 3  102  delete
 3  103  payment

In above example, I want to list cid for which payment and delete attributes exist but void attribute doesn’t exist. So it should list out 3 from above example because it doesn’t have void attribute.

Forgot to mention that there could be more attributes. However, I need to list out records for which delete and payment exist regardless of other attributes but void doesn’t.

Answer

I call this a “set-within-sets” query, because you are looking for particular sets of attributes within each cid.

I would express this with group by and conditions in the having:

select cid
from t
group by cid
having sum(case when attribute = 'payment' then 1 else 0 end) > 0 and
       sum(case when attribute = 'delete' then 1 else 0 end) > 0 and
       sum(case when attribute = 'void' then 1 else 0 end) = 0 ;

In some databases, you can simplify this with string aggregation — assuming there are no duplicate attributes for cids. For instance, using the MySQL function:

select cid
from t
where attribute in ('payment', 'delete' 'void')
group by cid
having group_concat(attribute order by attribute) = 'delete,payment';