6 Replies Latest reply: Oct 5, 2017 4:43 AM by Eamonn Hannon RSS

    Right Join - How do I get the excluded records

    Eamonn Hannon

      Hi,

       

      I am using a right join on 2 tables and I'm getting the expected results.  However, I'm trying to work out how I can get the excluded results.  I've created a sample scenario to explain my query:

       

      I have 2 tables as follows:

       

      Table 1                                    Table 2

      Code          Name                    Code              Name

      111             John                      111                John

      112             Paul                       112               Paul

      113             George                  113               George

      114             Pete                       115               Ringo

       

      When I right join Table 2 to Table 1 I get the following, which is correct:

       

      Table 1                                   

      Code          Name                   

      111             John

      112             Paul                      

      113             George                 

      115             Ringo

       

      What I would like to get is a table of what was excluded:

       

      Table 1                                   

      Code          Name                   

      114             Pete

       

      Can anyone advise me if and how this is possible?

       

      Thanks.

        • Re: Right Join - How do I get the excluded records
          Sunny Talwar

          May be this

           

          Table2:

          LOAD Code

          FROM...

           

          Table1:

          NoConcatenate

          LOAD Code,

               Name

          FROM

          Where Not Exists(Code);

           

          DROP Table Table2;

          • Re: Right Join - How do I get the excluded records
            Peter Cammaert

            Not using a JOIN I guess. A JOIN throws stuff together that has at least one value in common (no values in common is to be avoided)

             

            Try with a WHERE Not Exists() clause. You'll need either a field that uniquely identifies each individual records, or a composite key which you can create when you first load the data. Let's assume that the Code field can serve as Primary Key, then when you first load the data for Table 1 you can use something like:

             

            Table1:

            LOAD *

            FROM ... (...)

            WHERE Not Exists(Code);

             

            If Table1 is loaded from a resident table, you cannot use this trick because the values of the two Code field instances will be merged into the same symbol table. Exists() looks into the field symbol table to check whether a value has already been loaded. As a result, all values will always exist and no records will be stored in Table1. You'll first have to rename the Code field in the Table1 source table.

            • Re: Right Join - How do I get the excluded records
              Anand Chouhan

              Check this out and try to load table this way

               

              T1:

              LOAD * INLINE [

                  Code, Name

                  111, John

                  112, Paul

                  113, George

                  114, Pete

              ];

               

              T2:

              Concatenate

              LOAD * INLINE [

                  Code, Name

                  111, John

                  112, Paul

                  113, George

                  115, Ringo

              ];

               

              Left Join(T1)

              LOAD Name ,Count(Code) as Freq Resident T1 Group By Name;

               

              NoConcatenate

              LOAD * Resident T1 Where Freq=1;

              DROP Table T1;


              Op1.PNG

              • Re: Right Join - How do I get the excluded records
                Eamonn Hannon

                Hi Sunny,

                 

                Your answer worked perfectly on my sample data.  However, when I applied it to my real data it didn't work.  The table returns no entries and I don't know why.  The table has 5 columns but the scenario is the same.  There is a unique code which identifies each record.  Any suggestions why or what I should look out for?  Have I given you enough information?

                 

                Thanks,

                Eamonn.