5 Replies Latest reply: Sep 16, 2015 4:54 AM by Sasidhar Parupudi RSS

    Where not exists - How to SQL?

      Hello,

       

      I have a bizzare scenario.

       

      I have a table (and many other rows i dont wanna mention)

       

      T1:

      D_Key

      D_Key as Fod_Key_1

      from

      Transaktioner

      where type = 'f'

       

      concatenate (t1)

      Load

      D_Key

      D_Key as Fod_Key_1

      from Transaktioner

      where type = 'd'

      and not exists(Fod_Key_1,D_Key)

       

      drop field FOD_KEY_1;

       

       

       

      I simply cannot figure out how to repliacte this logic into SQL.

       

      T1 consists of 100.000 rows

      And the concatenated table consists of 6.000.000 rows.

       

      As logic i would think when these two are concatenated there would be no less than 5.900.000 rows. However there is like only 5.600.000 rows when my scripts end?

        • Re: Where not exists - How to SQL?
          neetha P

          hi thomas,

           

          try:

          T1:

          D_Key

          D_Key as Fod_Key_1

          from

          Transaktioner

          where type = 'f'

           

          concatenate (t1)

          Load

          D_Key

          D_Key as Fod_Key_1

          from Transaktioner

          where type = 'd'

          and not exists(D_Key)

          • Re: Where not exists - How to SQL?
            Claus Reinhard

            Hi Thomas,

             

            I am not 100% sure if I understood you correctly, but try this to statements.

             

            T1:

            Load

              *;

            SQL Select DISTINCT

              D_Key

              D_Key as Fod_Key_1

            from

              Transaktioner

            where

              type = 'f'

              Or type = 'd'

             

            The first one may work if your requested fields are always the same (no matter if type is f or d).

             

            T1:

            Load

              *;

            SQL Select DISTINCT

              D_Key

              D_Key as Fod_Key_1

            from

              Transaktioner

            where

              type = 'f'

              Or (type = 'd' and not exists (Select 1 from Transaktioner t2 where type = f and Transaktioner.D_Key = t2.D_Key)

             

            This may not be the fastest solution, but at least it should work.

             

            Cheers,

            Claus

              • Re: Where not exists - How to SQL?

                But how come when i load this table for it self

                 

                t2:

                load

                D_key

                ,d_Key as Fod_Key_1

                 

                from transkationer

                 

                where not exists(fod_key_1,D_key)

                 

                it returns less rows? When fod_key_1, and d_key is the same?

                 

                when only loading one table?

                 

                so without exists it has 6.000.000 rows.

                 

                but the exists it has 5.800.000 rows?