I've been reading through documentation (including the O'Reilly book links on the mysql.com website), and I'm sure this is simple, but I can't seem to find it.
I'm managing a mailing list for a client and I want to match on United States zip codes that correspond to different areas. (For non-US residents, the USPS used a 5 digit system for decades, then started a +4 system, which added 4 more digits to the original, so if your old zip was 12345, that would still work, but you could add 4 more digits to help for faster sorting -- like 12345-6789.) I'm using 2 tables, MailingList and ZipCodes. In MailingList I have the regular "exepected" columns -- Name, Street, City, State, and Zip. In ZipCodes I have ZipCodes, Name, District. The name is for the area the zip code covers and the district is the city or county it is in. For example, I am in the Bon Air zip code, but I'm in Chesterfield County, so the Name is Bon Air, but the District is Chesterfield. I want to be able to select all the names in MailingList in a specific county. Here is what I tried first: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE (M.Zip = Z.ZipCodes) AND (Z.District = "Chesterfield"); I did this and it pulled out 1 name, so I looked and realized a LOT of records had Zip+4, so they were 9 digit zip codes. So I tried to match only the first 5 characters of the Zip and ZipCode fields like this: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE ( left(M.Zip, 5) = left(Z.ZipCodes,5) ) AND (Z.District = "Chesterfield"); And it returns the same record, and nothing more. I tried it on 4 digits: SELECT Name, City, Zip FROM MailingList AS M, ZipCodes AS Z, WHERE ( left(M.Zip, 4) = left(Z.ZipCodes,4) ) AND (Z.District = "Chesterfield"); And it returned all the records it should have returned on 5 digits, but some of the records were retruned 6 times, some were returned only 1 time. So how can I select on a limited part of a field and why doesn't the 2nd line -- selecting the left 5 characters of both Zip and ZipCode work? Thank you! Hal --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
