Hi Bernard,
Thanks for that.
Yes, I knew the longstring = shortstring thing (it seems to work the otherway
around too), that's why I was confused that I wasn't getting anywhere. I think I
must've not trimmed the shortstring field, I'm either getting old or not drinking
enough coffee.
That'll be good enough for what I want, but what about the situation where I've got
both (eg) 087 and 0871 in the tariff table? I can imagine doing it with a coupla
extra queries (looking for the longest matching tariff for any numbers that turn up
in "two" tariffs), that certainly wouldn't be bad if it was the only way.
Regards
Mark
In article <#aSNhNBFJHA.4712@TK2MSFTNGP02.phx.gbl>, Bernhard Sander wrote:
> Hi Mark,
>
>> I'm sorry if it was confusing, I think these are the relevant parts
>> of the tables.
>>
>> Yes, they're all strings. Telephone numbers, numerics only, not that
>> it matters I think.
>>
>> The numbers in the tariff table are strings, just numbers.
>> The tariff table has numbers in it like "0871", or "079523". They're
>> not all the same length so "LEFT" and "SUBSTR" and the like aren't
>> easily used..
>>
>> And the calls table has telephone numbers like "01234566789",
>> "0871233454".
>
> SELECT Numbers.number,
Tariffs.code ;
> FROM Numbers LEFT OUTER JOIN Tariffs ON Numbers.number = trim(Tariffs.number) ;
> WHERE !empty(Tariffs.number)
>
> This works in pure Foxpro, not in other SQL dialects.
> In Foxpro, an expression like this:
> longString = shortString
> returns .T., when the longString at the left side starts with shortString on the
> right side of =
>
> A more complete (but not necessary in Foxpro) would be:
> SELECT Numbers.number,
Tariffs.code ;
> FROM Numbers LEFT OUTER JOIN Tariffs ON ;
> left(Numbers.number, len(trim(Tariffs.number)) = trim(Tariffs.number) ;
> WHERE !empty(Tariffs.number)
>
> LEFT OUTER JOIN returns .NULL. as
Tariffs.code for every row in Numbers where
> there is no matching row in Tariffs.
>
> You will get unexpected results, if there are number entries in TARIFF like
> 087
> 0871
> since the first entry will also match the telefon numbers that start with 0871
>
> Regards
> Bernhard Sander
