Tom, Change your database so that you have an engineer table and an engineer_zipcodes table. Each engineer can have multiple entries in the engineer_zipcodes table.
Engineer Engineer_id integer auto_increment primary key Name Address Etc Create index engineer1 on engineer(engineer_id) Engineer_zipcodes Engineer_id Min_zipcode Max_zipcode Create index zipcodes1 on engineer_zipcodes(engineer_id, min_zipcode, max_zipcode) To handle ranges of zipcodes, have two columns min_zipcode and max_zipcode for every entry. For single zip codes both columns are identical, for ranges they are different Then to locate an engineer: Select * from engineer e, engineer_zipcodes z where e.engineer_id = z.engineer_id and min_zipcode <= zzz and max_zipcode >= zzz zzz is the zipcode you're searching for. Hope this helps, Andy -----Original Message----- From: Tom Hesp [mailto:[EMAIL PROTECTED] Sent: 14 January 2004 12:56 To: [EMAIL PROTECTED] Subject: Searching for matching zipcode in a list of (ranges of) zipcodes Hi all, I am looking for a simple solution to find a zipcode in lists of zipcodes. I have a table with customer data including the customer's zipcode and a table containing information about service engineers. The service engineers can define a list of zipcode (ranges) of areas they want to (or are able to) service. An example of such a list is: 3528,3529,3612-3621,3828. This list is stored in one field in the service engineer table. What I would like to do is by using the customer's zipcode select all engineers that have that zipcode in their list. E.g. 3529 & 3615 would match while 3530 would not in the above example. I can of course code this in perl, for example, but that would mean that for every search I need to do I would have to retrieve the entire service engineer table and go through it to find a match. I was hoping MySQL would have function to this. I searched the documentation but have not been able to find any. Thanks for your time. Kind regards, Tom Hesp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
