left join but using first table data in where clause?

[ronaldwang]

I want to use left join to obtain some information from second table, but I also want to use the column from the first table in the where clause, is it possible? By directly using it I got error message, just wondering how to get through this?

The syntax is as below:

TableF:
load Column1,
Column2,
Column3
resident Table1;

left join
load Column1,
sum(Column4) as Column5
Resident table2
where table2.column6>MakeDate(2014,11,21)

      table2.column6<table1.column3
group by Column1;

[avinashelite]

the best approach is to use the mapping tables and map required columns from Table1 in Table2 then use the where clause and group by  , will give you the results

[Anil_Babu_Samineni]

that means, In Table2 you have some Qualify Statement? Isn't it

[ronaldwang]

no, I am not using qualify statement. the syntax just to show which table I am referring to.

[ronaldwang]

thanks, do you have link to any examples?

[Anil_Babu_Samineni]

To get alias names like Table2, You must need Qualify statement to bifurcate different level on condition

[ronaldwang]

Hi, Yes, even I have use qualify statement and being able to use table2.*, I still won't be able to refer back to table1 column from the left join statement.

[Anil_Babu_Samineni · Accepted solution]

What about this?

Table1:

Load Column1, Column2, Column3 From T1;

Qualify *;

Table2:

Load Column1, Column4, Column3, Column6

TableF:

load Column1,

Column2,

Column3

resident Table1;

left join (TableF)

load Table2.Column1,

sum(Table2.Column4) as Column5

Resident table2

where table2.column6>MakeDate(2014,11,21)

table2.column6<table1.column3

group by Column1;