2 Replies Latest reply: Mar 1, 2018 5:53 AM by Bram Knuever RSS

    Join with 3 tables

    Michael Anywar

      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

      [A],

      [B],

      [C], Common Field

      [D]

      Resident Table1;


      Left Join (Table2)

      Load

      A],Common field

      [B],

      [C]Common Field

      Resident Table2;

       

      Left Join(Table3)

      NoConcatenate

      Load

      A],Common Field

      [B]

      Resident Table3;

       

       

      DROP Table2;

      DROP Table3;

        • Re: Join with 3 tables
          Youssef Belloum

          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

          • Re: Join with 3 tables
            Bram Knuever

            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;