I'm writing a query that will return people based on the office they exist in. The data below is a made sample.
I need the result set of a query for everyone in office 'nyc', except for 'mike smith' because he is already in the office 'la'. I only want people returned for a given office if they don't already exist in another office.
So based on the data below, for the result set I would like
john doe and john lennon.
It seems like it should be simple but I'm having a hard time.