How can i Left Join() multiple tables?

[Mattia]

Hi guys,

i have four tables: A, B and C.

I need to bring all the fields of table C in table B and all the fields of table B in table A.

What's the right expression to do this?

Thanks,

Mattia

[simrankaur]

TableA:

load * from tableA;

left join

load * from TableB;

left join

load * from TableC;

[Yousef_Amarneh]

You need to make sure that the keys columns having the same name, i.e. if you want to join table A with table B using the column "EmployeeNo", this column called EmpNo in table A but it's called EmployeeNo in table B, you need to give alias to the column to have same name (case sensitive) then use the "Left Join" between the tables.

TableABC:

Load colum1, column2 as Key1

from TableA;

Left Join (TableABC)

Load colum3, column4, column5 as Key1, column6 as Key2

from TableB;

Left Join (TableABC)

Load colum7, column8, column9 as Key2

from TableC;

[Mattia]

Thanks for the replies.

Yousef, here is attached a sample data file.

Can you help me to insert the script?

I've tried but something don't work.

Thanks,

Mattia

[manishnarang]

Hi Mattia,

I can also help with this.. please paste your script here instead of the qvw.

[Yousef_Amarneh]

Use the below

TableABC:
LOAD * INLINE [
COUNT, CODE, NUM
00000001, 897, 1
00000001, 576, 2
00000001, 253, 3
00000002, 897, 1
00000002, 543, 2
00000003, 923, 1
];

Left Join (TableABC)
LOAD * INLINE [
COUNT, PROG, COM
0000001, 1, 999
0000001, 2, 999
0000002, 1, 555
0000003, 1, 222
];

Left Join (TableABC)
LOAD * INLINE [
COM, ASL, DIS
999, 030, LOM
555, 999
222, 010
];

and you will get the data shown in the attached

[Mattia]

Hi Manishnarang,

TableC:
LOAD * INLINE [
COM, ASL, DIS
999, 030, LOM
555, 999
222, 010
];

TableB:
LOAD * INLINE [
COUNT, PROG, COM
0000001, 1, 999
0000001, 2, 999
0000002, 1, 555
0000003, 1, 222
];

TableA:
LOAD * INLINE [
COUNT, CODE, NUM
00000001, 897, 1
00000001, 576, 2
00000001, 253, 3
00000002, 897, 1
00000002, 543, 2
00000003, 923, 1
];

[PrashantSangle]

what is your expected output???

your joining field don't have unique value so it will result into one to many relation.

Please check your data.

Regards,

Prashant Sangle

[Mattia]

Hi Yousef,

i can't see the attachment.

Error file system (-2147219196)

[Mattia]

Hi guys,

thanks to all for the replies and the suggests.

This is a part of a more complex project.

My goal is to compare two data flows:

1) FLOW1: tables TES, DET, COM and TAR

2) FLOW2: tables AMB1, AMB2, COM and TAR

The key fields betweet the 4 tables (TES/DET and AMB1/AMB2) are the same: COUNT and PRES

So, i want this: all the fields of tables COM and TAR both in tables TES and AMB2.

But, in the 1st flow, the table COM have a key field with the table TES, while the table TAR have a key field with the table DET that have a key field with table TES.

In the 2nd flow, the table TAR have a key field with the table AMB2, while the table COM have a key field with the table AMB1 that have a key field with table AMB2.

To have a better understand of what i need, i can attach a sample data file with all the tables involved.

Please, give me some time.

Thanks,

Mattia