Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > BOOK: Beginning SQL
|
BOOK: Beginning SQL
This is the forum to discuss the Wrox book Beginning SQL by Paul Wilton, John Colby; ISBN: 9780764577321
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning SQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 3rd, 2006, 06:51 PM
Registered User
 
Join Date: Aug 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Bad SQL in Try It Out in chapter 3

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?
 
Old August 18th, 2006, 06:39 PM
Registered User
 
Join Date: Aug 2006
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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;





Similar Threads
Thread Thread Starter Forum Replies Last Post
Chapter 12 SQL File? Any SQL files? ripcurlksm BOOK: Beginning PHP5, Apache, and MySQL Web Development ISBN: 978-0-7645-7966-0 0 June 20th, 2008 06:49 PM
Chapter 12, Try It Out, p.454, bad TagPrefix VictorVictor BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 3 February 23rd, 2006 02:39 PM
Why session_register() is bad. nikolai Pro PHP 17 November 21st, 2004 09:35 PM
Bad Codes from Chapter 3 and on, Please Help! GraphicArmy BOOK: Beginning PHP4/PHP 5 ISBN: 978-0-7645-4364-7; v5 ISBN: 978-0-7645-5783-5 15 May 3rd, 2004 05:07 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.