Can't figure out how to list all the people that don't live in same City as...
- by AspOnMyNet
I’d like to list all the people ( Person table ) that don’t live in same city as those cities listed in Location table. Thus, if Location table holds a record with City=’New York’ and State=’Moon’, but Person table holds a record with FirstName=’Someone’, City=’New York’ and Location=’Mars’, then Someone is listed in the resulting set, since she lives in New York located on Mars and not New York located on Moon, thus we’re talking about different cities with the same name. I tried solving it with the following query, but results are wrong:
SELECT Person.FirstName, Person.LastName,
Person.City, Person.State
FROM Person INNER JOIN Location
ON (Person.City <> Location.City AND Person.State = Location.State)
OR (Person.City = Location.City AND Person.State <> Location.State)
OR (Person.City <> Location.City AND Person.State <> Location.State)
ORDER BY Person.LastName;
Any ideas?
