1 Reply Latest reply: Jan 12, 2017 7:41 PM by Sunny Talwar RSS

    Join Where not isnull

    Joey Lutes

      I have 2 tables.  the requirement is simple - join the data ONLY where the data matches its counterpart.

       

      Table1:

       

      IDITEMColorSize
      1Thing1RedS
      2Thing2M
      3Thing3BlueS

       

      Table2:

      IDAltITEMAltColorAltSize
      1Thing11PurpleL
      2Thing12PinkL
      3Thing13BlueXL

       

      Sometimes there won't be a Color in Table 1.  If there isn't, I don't want the Altcolor populated in the FinalTable

      FinalTable:

      IDITEMColorSizeAltItemAltColorAltSie
      1Thing1RedSThing11PurpleL
      2Thing2MThing12L
      3Thing3BlueSThing13BlueXL

       

      I'm doing something wrong:

       

      Table1:

           Load

                ID, ITEM, Color, Size

          From Source;

      Left Join (Table1)

           Load

                ID, AltItem, Altsize

      From AltSource;

      Left Join (Table1)

           Load

                ID, AltColor

      From AltSource

      Where not isnull(Color);

       

      Error:  Field 'Color' not found

       

      Help?

      Thank you!

        • Re: Join Where not isnull
          Sunny Talwar

          May be this:

           

          Table1:

          Load ID,

               ITEM,

               Color,

               Size

          From Source;

           

          Left Join (Table1)

          Load ID,

               AltItem,

               Altsize,

               AllColor

          From AltSource;

           

          FinalTable:

          NoConcatenate

          LOAD ID,

               ITEM,

               Color,

               Size,

               AltItem,

               Altsize,

               If(Len(Trim(Color)) > 0, AllColor) as AllColor

          Resident Table1;

           

          DROP Table Table1;