left join issue

[#1]

hi all,

i have 3 tables and i want to left join but not getting desired results:

Table 1:

client  value

a          10

b          5    

c          6

table 2:

client     category

a               xxx

c               aaa

table 3:

client     category

b               yyy

desired result:

client     value category

a               10     xxx

b               5       yyy

c               6       aaa

when use left join, result is

client      value     category

a               10      xxx

b               5          -

c               6        aaa

left join statement used is below:

test:

load client,

       value

from table 1

left join

load client, category from table 2

left join

load client, category from table 3

final:

noconcatenate load

client,

value,

category from resident test

drop table test

please advise how to get desired result from the 3 tables using left join or some other way

thanks in advance

[Gysbert_Wassenaar · Accepted solution]

You need to concatenate table2 and table3 first and then right join it with table1.

test:

load client, category from table 2

load client, category from table 3

right join

load client,

       value

from table 1

final:

noconcatenate load

client,

value,

category from resident test

drop table test

The way you do it now won't work since after joining table1 and table2 the resulting table will have two fields and the join on table3 will use both fields for the joining because table3 also has these two fields.