Can't figure out how to list all the people that don't live in same City as...

Question

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?

Answer 1

Try this:

SELECT Person.FirstName, Person.LastName, Person.City, Person.State
    FROM Person INNER JOIN Location
    ON (Person.City = Location.City AND Person.State <> Location.State)
    ORDER BY Person.LastName;

This query finds all people that live in a city that has a matching City in the Location table but where the state is different. If you want to find People who have no matching Location there see Ayman's answer.

Answer 2

Do you mean something like

select person.fn, person.ln, person.city, person.st from person, location where person.city not in (select location.city from location where location.st != person.st)

Answer 3

Use a left join:

SELECT Person.FirstName, Person.LastName, Person.City, Person.State
FROM Person LEFT JOIN Location
ON (Person.City = Location.City AND Person.State = Location.State)
WHERE Location.City IS NULL
ORDER BY Person.LastName

Left joins include all rows from the "left" table (Person) even if there are no matches in the "right" table (Location). When there are no matches, the columns of the right table are set to NULL.

In this query, the WHERE condition limits the returned rows to those which don't have matches.

Answer 4

you should do a left outer join on the two tables, and then select the ones where the location is null

something like

select * from Person left outer join Location on p.city=l.city and p.state=l.state where l.id is null