4 Replies Latest reply: Sep 9, 2013 10:39 AM by Robert Ebner RSS

    Join Problem

    Robert Ebner

      Hi all,

       

      I have a question to the topic: "Join".

       

      I have two tables which look like that:

       

      table1

      IDPrice
      121217,40
      121315,30
      121428,30

       

      table2

      IDGroup
      1212Banana
      1212Apple
      1213Lemon

       

      Now I joined table2 with "left join" into table1.

      My problem is, that after the joining the ID 1212 will be doubled as there are two groups (Banana and Apple) possible. Now if I sum the prices of the different IDs, the result of ID 1212 is wrong cause of the problem.(17,40 +17,40)

       

      Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana :-) ??

       

      If you need further information, just let me know!

      Thank you for helping!

        • Re: Join Problem
          Stefan Wühl
          Do you have an idea how to solve this problem? Can I use another join, so that e.g. Apple is not joined and only banana :-) ??

           

          Sure, you can probably create a modified table2 with a distinct ID, where you have to decide which Group value to use.

           

          But have you tried, not joining the tables, instead keep them just linked / associated?

          • Re: Join Problem
            Gysbert Wassenaar

            You can use left keep  instead of left join

             

            You can try using a different aggregation function. For example avg(Price) instead of sum(Price)

             

            Or you can try something like this:

             

            Table:

            Load ID, Price

            from source1;

             

            left join

            Load ID, firstvalue(Group) as Group

            from source2

            group by ID;

            • Re: Join Problem
              j i

              Hello,

              You should create a unique key in table 2 and use that in table 1. Another idea is to create a super group and add the column "group" as a description:

               

              IDsuper groupdescription
              1212Banana-AppleBananas and apples
              1213Lemon-OrangesLemon

               

              I hope this helps.

              J