Hi everybody,

I want to bring fields from table 2 to table 1.
Table 1 contains sales-data, turnover, etc.

customer, invoice_nr, object_nr(level1), sub_sub_object_nr(level3), ..... turnover

round about 2.5 mio records.

Table 2 contains "mappings"
level1, level2, level3
object_nr, sub_object_nr, sub_sub_object_nr, procentual_part

round about 50.000 records.

Example:
T1:
customer, invoice_nr, object_nr, sub_sub_object_nr, ..... turnover
...... 307202 1 1000.00$

T2:
object_nr, sub_object_nr, sub_sub_object_nr, procentual_part
307202 300035 1 0,10
307202 300035 2 0,20
307202 300035 3 0,10
307202 300035 4 0,05
307202 300035 5 0,10
307202 300035 6 0,10
..
307202 300036 10 ....
307202 300036 11
307202 300036 12
..

402524 300041 no entry 0,80
402524 400524 no entry 0,20

So far, so good.

The problem is that there are three possible situations:
1. object_nr will find 2 sub_object_nr
with NO sub_sub_object_nr and e.g. a 80/20 percentage.

2. object_nr will find many sub_object_nr
with even more sub_sub_object_nr and xx percentage.

3. the awful part.
object_nr will find many sub_object_nr
with sub_sub_object_nr and xx percentage
BUT some records without sub_sub_object_nr.

Now, if I join only level1
situation 1 is solved

If I join level1 + level2
situation 2 is solved.

But how to solve situation 3???

I tried a left outer join but
that will not work.
Perhaps I have to create a procedure??

Any idea?
Any hints are apreciated.

yours

Mike