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')