SELECT one-to-many help

[KaneMcConnell]

Working on a new project that's forcing me to learn a few new tricks (always fun!).  One thing I haven't played with too much is a one-to-many SELECT statement where I only want to create one result.  I typically  use subqueries in PHP but with the amount of data in this project, performance is becoming an issue.  Here's the relationship I'm trying to set up.  Can someone please help formulate the best method for the SELECT statement?

 

Table "carriers":

DocketCarrierCityState

123456CharlieLos AngelesCA

 

Table "zipcodes":

CityStateZipCode

Los AngelesCA90001

Los AngelesCA90002

Los AngelesCA90003

 

Here's my need.  The carriers table is imported from the US DOT with no zip codes (love government data!).  All we get is a city, state.  But we have a zipcodes table also imported from USPS.  I need to match the carriers city,state to a zipcode in the zipcodes table so that I get a row like this:

 

123456CharlieLos AngelesCA90001

 

Here's my issue.  There are 218 unique zipcodes for Los Angeles, CA.  How can I create a SELECT statement that just finds the first matching zipcode and puts it in the resulting row.  It's fine if it's not the exact zipcode that you'd use to send a piece of mail, as long as it's one of the Los Angeles, CA zipcodes it will serve it's purpose.

 

Because my next step then will be use the cross reference to show me all my carriers with zipcodes LIKE '90%' so that I can work with carriers in a specific region.

 

MySQL version: 5.0.45

[KaneMcConnell]

Here's what I've been trying:

 

EXPLAIN SELECT * FROM mx_fmcsa_carriers LEFT JOIN zip_codes ON mx_fmcsa_carriers.City = zip_codes.City && mx_fmcsa_carriers.State = zip_codes.State

 

And the explain result returns:

 

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra

1SIMPLEmx_fmcsa_carriersALLNULLNULLNULLNULL60931

1SIMPLEzip_codesALLNULLNULLNULLNULL80472

 

And the MySQL error is:

 

Error
SQL query: 

SELECT * 
FROM mx_fmcsa_carriers
LEFT JOIN zip_codes ON mx_fmcsa_carriers.City = zip_codes.City && mx_fmcsa_carriers.State = zip_codes.State
LIMIT 0 , 30

MySQL said: 

#1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay 

[fenway]

In this case, a sub-select with a LIMIT 1 should do the trick.

[KaneMcConnell]

Oh! I'm an idiot.  Why is the simplest problems are the ones that you stare at the longest??  Thanks very much, works great!