6 Replies Latest reply: May 7, 2014 5:33 AM by Biren Agrawalla RSS

    Join ?

    Helen Betty

      I have two tables , TableA & TableB

       

      I want the all the records in TableA but exclude the common records in both the Tables.

        • Re: Join ?
          Anand Chouhan

          Load table like this

           

           

          TableA:

          LOAD * Inline

          [

          C1

          A

          B

          C

          ];

           

          TableB:

          LOAD * Inline

          [

          C2

          A

          B

          E

          F

          ] Where not Exists(C1,C2);

          • Re: Join ?
            sujeet singh

            Table1:

            LOAD * INLINE [

                ID, Sales

                1, 23

                2, 4

                3, 6

                4, 9

                5, 12

                6, 9

            ];

             

             

            Table2:NoConcatenate

            LOAD * INLINE [

                ID, Sales

                1, 23

                2, 4

                13, 6

                14, 9

                7, 12

                9, 9

            ];

             

             

            Join(Table1)

            LOAD

            ID, Sales

            ,ID as ID2

            Resident Table2;

            DROP Table Table2;

             

             

            MainData:NoConcatenate

            LOAD

            ID, Sales

             

             

            Resident Table1 where IsNull(ID2)<>0 ;

            DROP Table Table1;

            • Re: Join ?
              jagan mohan rao appala

              Hi,

               

              You can use Where not Exists() as Anand suggested, if not then attach some sample data and expected output.

               

              Regards,

              Jagan.

              • Re: Join ?
                Anand Chouhan

                Load the tables like below script this will remove the duplicate records from your tables means common records and keep only uncommon records

                 

                Temp:

                LOAD *,'Tab1' as Flag;

                LOAD * Inline

                [ID

                A

                B

                C

                F];

                 

                Join

                 

                TableB:

                LOAD *,'Tab2' as Flag;

                LOAD * Inline

                [ID

                A

                B

                C

                D

                E];

                 

                NoConcatenate

                Temp2:

                LOAD

                ID,

                Count(Flag) as Flag

                Resident Temp

                Group By ID

                Order By ID;

                 

                Final:

                LOAD

                ID

                Resident Temp2

                Where Flag=1;

                 

                DROP Tables Temp,Temp2;

                 

                Output you get

                 

                ID

                D

                E

                F

                 

                which is uncommon it both tables.

                • Re: Join ?
                  anbu cheliyan

                  In Sql

                  Select data from TableA excluding common records between TableA and TableB

                  Select col1,col2 from TableA

                  Minus

                  select col1,col2 from TableB

                  • Re: Join ?
                    Biren Agrawalla

                    Hii,

                    Simply just use the function not Exist().

                    If not then you can try the other methods as well.