Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.