Jump to content

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/242079-select-one-to-many-help/
Share on other sites

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 

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.