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

by

[ad_1]

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

[ad_2]

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