Hello all. I have a simple quoting system I am developing for a project, and need help with how to query rates. There are two tables, one for zips (with state, county, zip code, and a zip lookup code associated with each zip), and one for rate data (company name, plan letter, age, gender, tobacco/no-tobacco, zip lookup code, and monthly rate).

The quoting process begins with the user selecting a zip code, plan, company, gender, and tobacco. My question, is how am I supposed to join the two tables to query a rate based on zip code/zip lookup code and display the rate? I am really confused. I've done simple mysql queries before, but never anything that needed joining. Will paypal anyone who helps me out.

Here is a diagram I made on how the database is setup.


5 Years
Discussion Span
Last Post by JorgeM

Well, based on your fields, it appears that you would join the two tables based on the ZIP_LOOKUP_CODE. I assume that data is different than what is stored in Zip.

In any event, the following SQL query simply joins the two tables and includes a WHERE clause so that you can specify the zip code and only return those records that match the specific zip code. You can start with this and just remove the fields that you have no interest in.

SELECT  data.Company, data.[Plan], data.ZIP_LOOKUP_CODE, data.Gender, data.Tobacco, data.Age, data.Monthly_Rate, zips.ZIP_LOOKUP_CODE AS Expr1, zips.State, 
           zips.County, zips.City, zips.Zip
           zips ON data.ZIP_LOOKUP_CODE = zips.ZIP_LOOKUP_CODE
WHERE   (zips.Zip = @zip)
Votes + Comments
Thanks so much!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.