Bad SQL in Try It Out in chapter 3

punch · August 3rd, 2006, 06:51 PM

In the Try It Out on page 108, no. 1, there is an SQL statement that reads:

Code:

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

This is supposed to return all members who don't live in a city where the meetings are held -- that is, all records from the MemberDetails table whose City+State isn't in the Location table.

If there's a city with the same name in another state, it's not the same city. And if there's a city with a different name in the same state, it's not the same city. The problem is that this SQL doesn't take into account cities that have different names that AREN'T in the same state! That is, both the city names and the state names are different. Try adding a member who lives in a city and state that are both different from any in the Location table, and that member should be returned but isn't.

Adding "OR (MemberDetails.City <> Location.City And MemberDetails.State <> Location.State)" to the ON clause yields way too many records, because every record in one table is compared to every record in the other table.

The only solution I can come up with (and the way I've been used to doing this kind of thing in Access, not even knowing before that you could actually use <> in ON clauses) is with a LEFT JOIN as follows:

Code:

SELECT MemberDetails.FirstName, MemberDetails.LastName, MemberDetails.City, MemberDetails.State
FROM MemberDetails LEFT JOIN Location
ON MemberDetails.State = Location.State AND MemberDetails.City = Location.City
WHERE (Location.City Is Null)
ORDER BY MemberDetails.LastName;

Does Anybody have any input?

punch · August 18th, 2006, 06:39 PM

After reading further in the book and learning about EXISTS, here's another way to get the same results:

SELECT FirstName, LastName, City, State
FROM MemberDetails
WHERE NOT EXISTS
    (SELECT City, State
     FROM Location
     WHERE City = MemberDetails.City AND State = MemberDetails.State)
ORDER BY MemberDetails.LastName;