Join two tables using ID in bridge table

[johnnyjohn]

Hi, 

I have the following data model.

I'd like to join table 1 and table 2, so that my final table is table 1 with an extra attribute which is the 2_volume field from table 2, and joined on the 1_venue and 2_venue. What's the easier way to do this in the load script?

[vinieme12]

What is the cardinality between Table1 and bridge and bridge and Table 2 ??

[Ruhulessin]

Hi @johnnyjohn,

Do I understand clear that 1_venue and 2_venue are the key fields that can be joined?
If so, then you can left join [table 2] to [table 1] by renaming 2_venue as 1_venue.

LEFT JOIN ([table 1])
LOAD
2_venue AS 1_venue
,2_volume
FROM ......
;

[johnnyjohn]

Table 1 -> Bridge : One to Many

Bridge -> Table 2 : Many to One

[johnnyjohn]

Thanks, I'm not sure this takes into account the bridge table dependency though - Table 1 and Table 2 are linked via the bridge table, and only when those are linked, should the mapping be done

[vinieme12]

Solution 1

bridge:
load 1_id,2_id
From bridgeSource;

left join(bridge)
2_id,2_volume,2_venue
From table2Source;


MapVol:
Mapping Load 1_id,2_volume Resident bridge;

MapVenue:
Mapping Load 1_id,2_venue Resident bridge;
Drop table bridge;


table1:
Load
1_id,1_volume,1_venue
,Applymap('MapVol',1_id) as 2_volume
,Applymap('MapVenue',1_id) as 2_venue
From table1Source;



exit Script;

[vinieme12 · Accepted solution]

Solution 2

bridge:
load 1_id,2_id
From bridgeSource;

left join(bridge)
2_id,2_volume,2_venue
From table2Source;

table1:
Load
1_id,1_volume,1_venue
From table1Source;

Left Join(table1)
Load Distinct id_1,2_volume,2_venue Resident bridge;
drop table bridge;


exit Script;

[johnnyjohn]

Thank you, that seems to be creating synthetic keys and a loop however. Does the second solution include the joining using the venue ?

[vinieme12]

how is this creating a synthetic key ???

can you post a snapshot of your model or provide some sample data?

[johnnyjohn]

All good sir, had to change some of the code around a little. Thanks