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!

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

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