MySQL multi-table query

Greetings! I'm having difficulties creating a query that references multiple tables. I'm new to SQL and joins are stumping me. I've mucked about Google and after searching around I discovered Joining tables in SQL which got me closer to where I want to be! The query I've created isn't doing what I want. I have two tables:

disp_profile
    disp_id*  name      address      zip
    0001      Profile1  SomeAddress1 11111
    0002      Profile2  SomeAddress2 22222
    0003      Profile3  SomeAddress3 33333

zipcode
    zip_code* state     city         county
    11111     CA        City1        County1
    22222     WA        City2        County2
    33333     NV        City3        County3

What I am attempting to do is grab the City, State, County from zipcode when the zipcode.zip = disp_profile.zip (filtered by a variable State). Ideally returning something like:

    dispId    dispName     dispAddress     dispZip    zipState    zipCounty    zipCity  zipCode
    001       Profile1     SomeAddress1    11111      CA          County1      City1    11111
or
    002       Profile2     SomeAddress2    22222      WA          County2      City2    22222


SELECT
    dp.disp_id AS dispId, dp.name AS dispName, dp.address1 AS dispAddress1, dp.zip AS dispZip, zc.state AS zipState, zc.county AS zipCounty, zc.city AS zipCity, zc.zip_code AS zipCode
FROM
    disp_profile dp
INNER JOIN
    zipcodes zc
ON
    dp.zip = zc.zip_code
WHERE
    dp.state = 'CA'

I'm aware this may not be the best way to go about this but I thought it开发者_高级运维 better to store things separately vs redundant information for each item. The dataset in disp_profile is about 1000 records and zip_codes is about 30,000. Would this be done easier with a subquery? Thanks for your time!

No, you are doing it fine. This is what joins where made for, no need to use a subquery here. You could, but it would not perform better (the MySQL Query Optimizer might even translate it to a join internally).

If you worry about speed: Put the word "EXPLAIN" in front of your SELECT statement to see what MySQL is doing:

EXPLAIN SELECT
    dp.disp_id AS dispId, dp.name AS dispName, dp.address1 AS dispAddress1, dp.zip AS dispZip, zc.state AS zipState, zc.county AS zipCounty, zc.city AS zipCity, zc.zip_code AS zipCode
FROM
    disp_profile dp
INNER JOIN
    zipcodes zc
ON
    dp.zip = zc.zip_code
WHERE
    dp.state = 'CA'

It will tell you how your statement is being executed. Paste the output here if you want us to help interpreting :)

That looks okay to me, at least at first glance. I prefer the old school way of:

select dp.disp_id AS dispId,
       dp.name AS dispName,
       dp.address1 AS dispAddress1,
       zc.state AS zipState,
       zc.county AS zipCounty,
       zc.city AS zipCity,
       zc.zip_code AS zipCode
from   disp_profile dp,
       zipcodes zc
where  dp.state = 'CA'
  and  dp.zip = zc.zip_code

but that's just a matter of style (and using a DBMS with a very intelligent optimiser - whether MySQL is a match for my DBMS of choice, I couldn't comment (but I doubt it)).

The one change I have made (and which you probably should) is to only get one of the ZIP codes. It's redundant getting the field from both dp and zc since they're identical due to your join.