Dovi:
The problem we are having in replying is following just exactly what you are trying to do. I will attempt to paraphrase and reshape your process so we can try to reach a solution.
Rates table:
<b>code country rate1 rate2 rate3 rateN</b>
011578 Colombia .23 .21 .28 .32
011789 Peru .31 .33 .30 .38
Now, you have 1 customer that makes a call that starts with the code 011575 and gets charged at the RATE 3 amount. A different calls starting with the same code of 011575 but gets charged at the RATE 4 amount.
The questions I have are:
1. Who or what determines the rate a customer is charged?
2. Is any data stored in the Customer table to indicate what Rate plan they are on?
I do not think you have a proper data design to accomplish what you want. I think you need a child table to the Customer table. Let's call it RatePlan. The Rates table would then be a child to the RatePlan table. The structure for the RatePlan table is:
KeyID
RateKey
CustID
You would have 1 record in the RatePlan table for each Code the customer would be calling. You would then retrieve the actual Rate amount from the Rates table.
I would change the structure of the Rates table to:
KeyID
RateID
RateCode
CountryID
RateAmount
I would then have 1 record for each different rate charged per RateCode. The parent table, RatePlan, is related into this table by RateKey. I would also have a Country table that has one entry per country name with a KeyID field. The CountryID in the Rates table is the Foreign Key equal to the Country.KeyID field.
If you do not do it this way, then you would have to have in the Customer table a field for every Code in the Rates table with the number of the Rate [Rate1, Rate2] that applies to that customer.
There is no SQL that will do what you want with the way the tables are designed now.
Mark McCasland
Midlothian, TX USA