Inner Join before Left Join?

[greend21]

Currently, I am loading table one and then left joining table two to table one. Now what I need to do is load table one, load table two, inner join table two to a table three(which is a load of a subset of table two), and then left join this back to table one. How would I best put this in the script?

[marinadorcassio1]

Hi Dan,

Maybe something like :

Table1:

LOAD *

From Table1;

Table3:

LOAD *

Where...

From Table2;

inner join (Table3)

LOAD *

From Table2;

left join(Table1)

LOAD *

Resident Table3;

Drop table Table3;

I think that's quite performing.

Best regards,

Marina

[jensmunnichs · Accepted solution]

If table3 is based on table2, maybe you could solve this using a preceding load?

Table1:

LOAD fields

FROM source

Left join (Table1)

Table2and3:

LOAD *,

     Calculation(field1) as CalcField1,

     Calculation(field2) as CalcField2,

     Etc..;

LOAD field1, field2

FROM <source>;

Or, if you don't want to do that, you could Load table2, load and inner join table3, then load and right join table1.

[greend21]

The right join seems to be the simplest method.

I noticed if I said Right Join(Table1) it said Table1 was not found. It would have to look like this for the loads and if I want to rename the table right?

Table2:

Load *

From source;

Inner Join(Table2)

Table3:

Load *

From source;

Right Join (Table2)

Table1:

Load *

From Source;

Rename Table Table2 to Table1;

[jensmunnichs]

Yes, sorry I don't think what I wrote was very clear. What I meant in the last sentence was 'then load and right join table1 (to table2)' so Right Join (Table2) is correct. Right Join (Table1) doesn't work because Table1 hasn't been loaded yet at that point.

Also yes your renaming statement seems correct as well. Have you tried this script yet to see if it works?

[greend21]

The script works. I was just about to validate the data still looked as expected from the joins. Thanks!