Skip to main content
Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for 
Search instead for 
Did you mean: 
michael_anywar
Creator
Creator

Join with 3 tables

I need some help. I have 3 tables in my editor. and loaded. as seen below. Now i want to create a join table with Specific fields. Somebody guide newbie.

Thanks.

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root"

WITH CONNECTION();

NEXT startAt;

[Table1]:

LOAD

"Field1"AS "A",

"Field2"AS "B",

"Field3"AS "C",

"Field4" AS "D"

RESIDENT RestConnectorMasterTable;

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root2"

WITH CONNECTION();

NEXT startAt;

[Table2]:

LOAD

"Field1" AS "A",

"Field2"AS "B",

"Field4" AS "C"

RESIDENT RestConnectorMasterTable;


//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root3"

WITH CONNECTION();

NEXT startAt;

[Table3]:

LOAD

"Field1" AS "A",

"Field2"  AS "B",

"Field4"AS "C",

RESIDENT RestConnectorMasterTable;

//::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::


So The ResidentMaster Tables of  Table1 has a common Field with the ResidentMaster Tables of both Table2 and Table3.

I would want to create a Joint table with specific fields though.

Below is my current statement. Can i write the Join statement after loading all the tables..? or.....somewhere in the middle. And would it be correct..?

.................................................

....................................................

Activities_join:

NoConcatenate

Load

,

,

, Common Field

Resident Table1;


Left Join (Table2)

Load

A],Common field

,

Common Field

Resident Table2;

Left Join(Table3)

NoConcatenate

Load

A],Common Field

Resident Table3;

DROP Table2;

DROP Table3;

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

Hi,

here after you first load your third table (table3) after the last resident, you will have a model like this

PS: don't forget to drop the RestConnectorMasterTable table(s) !

model.png

then you can concatenate your table and use flags, you will have something like this:

model2.png

View solution in original post

2 Replies
YoussefBelloum
Champion
Champion

Hi,

here after you first load your third table (table3) after the last resident, you will have a model like this

PS: don't forget to drop the RestConnectorMasterTable table(s) !

model.png

then you can concatenate your table and use flags, you will have something like this:

model2.png

bramkn
Partner - Specialist
Partner - Specialist

Depending on what data you want to keep you can just join the tables to eachother when ever you want. so for example you can:

RestConnectorMasterTable:

SQL SELECT

"Field1",

"Field2",

"Field3",

"Field4",

FROM JSON (wrap on) "root2"

WITH CONNECTION();

NEXT startAt;

LEFT JOIN (Table1)

LOAD

"Field1" AS "A",

"Field2"AS "B",

"Field4" AS "C"

RESIDENT RestConnectorMasterTable;