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      && Surrogate Primary Key for each record
RateKey    && Foreign Key equal to the KeyID of the Rates table
CustID     && Foreigh Key equal to the CustID of the Customer table

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.