Member Avatar for gomedigap

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.

http://i.imgur.com/TLIZx.jpg

  • 2 Contributors
  • 1 Reply
  • 157 Views
  • 28 Minutes Discussion Span
  • Latest Post Latest Post by JorgeM
Member Avatar for 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
FROM    data INNER JOIN
           zips ON data.ZIP_LOOKUP_CODE = zips.ZIP_LOOKUP_CODE
WHERE   (zips.Zip = @zip)
commented: Thanks so much! +0
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.