Joins

[Anonymous]

I have three tables. The Division table is joined by RegionData1 for the Data1 table and RegionData2 for the Data2 table. I do not want to see values for the Division table if the values do not exist in the data tables. I'm asusming a join is needed but not sure how to join.

Tables:

Division

Data1

Data2

*The example shown is in Excel but this is actually a SQL Script

[Anonymous]

Hi mrJohn,

Load first Regions tables, and then Division with exists on where:

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Data1);

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Data2);

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Division)
WHERE EXISTS(regionData1) or Exists(regionData2);

Regards!

[florentina_doga]

try this

final:

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Division);

left join

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Data1);

left join

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Data2);

noconcatenate

final1:

load *

resident final where len(IdPK1)<>0 or len(IdPK2)<>0;

drop table final;

[sunny_talwar]

What would you want the final output to look like?

[Anonymous]

Essentailly a LEFT JOIN for both Data1 and Data2 to the Divisions Table.

[sunny_talwar]

What the final output need to look like?

Like this?

1	East Central	East
2	East Central	East

[Anonymous]

Correct