Search code examples
sqlrelational-databasesap-ase

SQL select id where other columns are identical

If I have a table of properties with columns owner, wallColor and roofColor (let's say one owner can own multiple houses but houses can have only one owner), how can I select all owners who only have houses that have identical wall and roof colors?

So if I had

owner   wallColor  roofColor
Bob     Red        Green
Bob     Blue       Blue
Greg    Yellow     Yellow
Greg    Black      Black

it should only return Greg, because Bob has a house with red walls and a green roof.

Solution 
  •  SELECT DISTINCT t1.owner
 FROM tablename t1
 WHERE t1.owner NOT IN
 (
    SELECT DISTINCT t2.owner FROM tablename t2
    WHERE t2.wallColor != t2.roofColor
 )