sqlvertica

SQL filter if ID doesn't have a value in another column

I have this sample table

Payment ID Type
123 Fee
123 Service
123 Finance
456 Fee
456 Service

I'm trying to achieve a table that would filter out any row where an ID doesn't have "type" "Finance".

Expected result would be

Payment ID Type
456 Fee
456 Service
Solution

  • A readable alternative is:

    with
filter_table as (
select payment_id
from your_table
where type = 'Finance'
)    
select *
from your_table
where payment_id is not in (select id from filter_table)

    An alternative without the sub-query could be:

    select *
from your_table
where payment_id is not in (select id from your_table where type='Finance')