create multiable ranked keys

[amien]

i have two tables. Each table contains 3 unique key fields to link both tables. Normally i would create a new key based on these 3 values. All 3 keys are unique keys

The only problem is, that for each row, not all 3 fields are filled. it possible that all 3 are filled, but not always.

so, if would have 3 keys:

key1, key2, key3 (most perfect link)

key1, key2 (good link)

key2, key 3 (good link)

key1, key 3 (good link)

key1 (worst link)

key2 (worst link)

key3 (worst link)

the first one should apply first, then the recond one etc. etc.

how can i achieve this in my load script?

in the end i also want to know, per row, on what level both tables are linked

[#2]

Hi Amien,

I would do something like this:

if(not isnull(key1) and not isnull(key2) and not isnull(key3),Key1|Key2,Key3,

     if(not isnull(key1) and isnull(key2) and isnull(key3),Key2|Key3  etc.......

Succes,

Halmar

[amien]

Thanks for your reply

Yes, but its possible that in table 1 i have 2 keys and for table 2 only 1 key .. for the same item.

Those won't be linked with your solution. Because you assume that in both tables the same amount of keys are filled.

example on SAME ITEM and all keys are unique within the same table:

table1

--------

key1 : filled

key2 : filled

key3 : NULL

table 2

----------

key 1: filled

key 2 : NULL

key 3 : NULL