Search code examples
sqldatabasepostgresqlnested-select

Conditional Postgres Query

The PG table looks like this:

id - name   - type
1  - Name 1 - Type A
2  - Name 1 - Type B
3  - Name 2 - Type A
4  - Name 2 - Type B
5  - Name 3 - Type A

I would like to write a query that only lists rows in which Name has a 'Type A' record but not a Type B record.

This is the result I am hoping for:

5  - Name 3 - Type A
Solution

  • You can use a nested select:

    select t.*
from table_name t
where not exists(
    select 1
    from table_name it
    where t.name = it.name
    and it.type = 'Type B'
)
and t.type = 'Type A'