Run multiple MySQL queries based on a series of ifs

I am just getting started on this complex query I need to write and was hoping for any suggestions or feedback regarding table structure and the actual query itself..

I've already created my tables and populated test data, and now just trying to sort out how and what is possible within MySQL.

Here is an outline of the problem:

End result: Listing of rates based on specific queried criteria (see below):

Age: [ 27 ]<br>
Spouse Age: [ 25 ]<br>
Num of Children: [ 开发者_StackOverflow3 ]<br>
Zip Code: [ 97128 ]

The problem I am running into is each company that provides rates has a unique way of dealing with the rate. And I am looking for the best approach for multiple queries based on the company (one query with results for each company more or less all combined into one result set).

Here are some facts:

• Each company deals with zip code ranges which assist in the query result.
• Each company has a different method of calculating the rate based on the Applicant, Spouse, Num of Children: Example,
• Company A determines rate by: Applicant + Spouse + Child(ren) = rate (age is pertinent to the applicant within a range).
• Company B determines the rate by total number of applicants like: 1, 2, 3, 4, 5, 6+ = rate (and age is ignored).

First off, what would I call this type of query? Multiple nested query? And should I intertwine php within it to determine the If()s .

Its just 2 (or however many) queries. Not called anything special or technical. If you have 3 companies, you'll just do 3 queries. etc. You're not going to want a bunch of logic in your query. Some is ok, but sometimes it's better to do it in PHP than MySQL. Sometimes 3 queries are better than one.

You'll use php to construct it all. You'll do each query, and then build your results set as an array. After all 3 queries have run, you'll have added to the results output array 3 times. Then loop through that and display it.

In the spirit of giving you what you wanted, you'll probably be looking at union queries. Basically you make n separate queries, joined by union, which all return the same fields, and you can loop through it as one result, and get all your data from mysql in one shot.

Edit

In response to your comment, here is a little pseudo code to get you in the right direction.

query for company 1
loop through results
  put values needed for display in array

query for company 2
loop through results
  put values needed for display in array

loop through array
  print results

Assuming there are not a lot of companies and the calculations are not particularly complex, you should be able to write a query that is fairly maintainable by using CASE statement that performs a different calculation based on the company type.

You should be able to do all your calculations in SQL. This looks like a good scenario for using a stored procedure where you can collect all of the data using the various methods required by each company and return a result set.

select [calculate rate] rate from company1_table
union all
select [calculate rate2] rate from company2_table
.
.
.