Omit record in group if another record is present in same group

Question

I'm trying to remove a record from a group of results, if there is a record with a certain value within the same group. I tried to adapt my complex problem to a simple example:

DECLARE @fruits TABLE (type varchar(16), attribute varchar(16))
INSERT INTO @fruits VALUES('orange', 'juicy');
INSERT INTO @fruits VALUES('orange', 'seeds');
INSERT INTO @fruits VALUES('orange', 'pit');
INSERT INTO @fruits VALUES('apple', 'juicy');
INSERT INTO @fruits VALUES('apple', 'seeds');
INSERT INTO @fruits VALUES('apple', 'crisp');
SELECT * FROM @fruits;

Let's say I wanted to omit any record from my results that has an attribute='pit' if there is another fruit of the same type with attribute='seeds'.

How can I go about writing that query using SQL Server 2016?

Answer 1

You can use note exists and a little bit of boolean logic:

select f.*
from @fruits f
where 
    attribute <> 'pit'
    or not exists (
        select 1 
        from @fruits f1 
        where f1.type = f.type and f1.attribute = 'seeds'
    )

This filters out records of attribute "pit" when the given type also has attribute "seeds".

It might be simpler to follow if the condition is expressed as a negation:

select f.*
from @fruits f
where not (
    attribute = 'pit'
    and exists (
        select 1 
        from @fruits f1 
        where f1.type = f.type and f1.attribute = 'seeds'
    )

An alternative uses window functions:

select * 
from (
    select 
        f.*, 
        max(case when attribute = 'seeds' then 1 else 0 end) over(partition by type) has_seeds
    from @fruits f
) f
where not (attribute = 'pit' and has_seeds = 1)