[Solved] How do you check for matching value in third column based on distinct combinations of other two columns?

by


You can group by building, location for the rows where object in ('WALL', 'WINDOW'):

select building, location, 'FLAG' action
from tablename
where object in ('WALL', 'WINDOW')
group by building, location
having count(distinct object) < 2

The condition count(distinct object) < 2 in the having clause returns combination of building, location where 'WALL' and 'WINDOW' do not both exist.
See the demo.
Results:

| building | location | action |
| -------- | -------- | ------ |
| A        | FLOOR2   | FLAG   |
| B        | FLOOR1   | FLAG   |

Or with NOT EXISTS:

select t.building, t.location, 'FLAG' action
from tablename t
where object in ('WALL', 'WINDOW')
and not exists (
  select 1 from tablename
  where building = t.building and location = t.location and object <> t.object
)

See the demo.

2

solved How do you check for matching value in third column based on distinct combinations of other two columns?