3 Replies Latest reply: Jul 31, 2013 7:38 AM by stephane jeanneteau RSS

    several links with the same table

      hello,

       

      I've got a simple question about link between two tables.

      I am beginner with SQL and I think my question is easy for someone who is not beginner

       

      The first table is like this :

       

      Interactions:

      LOAD AccountId ,

          Assignee_Support_Initial__c ,

          CreatedById ,

          LastModifiedById ;

      SQL SELECT AccountId,

          Assignee_Support_Initial__c,

          CreatedById,

          LastModifiedById 

      FROM Case;

       

      Assignee_Support_Initial__c, CreatedById, LastModifiedById   are a code where i want to put the name of the user which is on the second table. ( id of the second table is the link with Assignee_Support_Initial__c, CreatedById, LastModifiedById )

       

      operateur:

      LOAD Email,

          FirstName ,

          Id ,

          LastName;

      SQL SELECT Email,

          FirstName,

          Id,

          LastName

      FROM User;

       

      I would like to add the lastname in the interactions table (join left?). how i can do that?

       

           AccountId ,

           Assignee_Support_Initial__c ,

           Lastname_Assignee_Support_Initial,

           CreatedById,

           Lastname_Assignee_Support_Initial

           LastModifiedById,

           Lastname_LastModifiedById

       

       

      thank you

       

      stephane

        • Re: several links with the same table

          Hi,

           

          Try this.

           

          Interactions:

          LOAD AccountId ,

              Assignee_Support_Initial__c ,

              CreatedById ,

              LastModifiedById,

          Assignee_Support_Initial__c &  CreatedById & LastModifiedById as Id;

          ;

          SQL SELECT AccountId,

              Assignee_Support_Initial__c,

              CreatedById,

              LastModifiedById 

          FROM Case;

           

          LEFT JOIN

          LOAD Email,

              FirstName ,

              Id ,

              LastName;

          SQL SELECT Email,

              FirstName,

              Id,

              LastName

          FROM User;

            • Re: several links with the same table

              Sorry venugopal4s, but it doesn't work.

               

              The value for theses fields are not the same :

              Assignee_Support_Initial__c ,

                  CreatedById ,

                  LastModifiedById,

               

              that means differents persons could be the actor of theses fields.

              The list of the persons are on the seconf table, but I want only to put the right person name for each field in the first table.

               

              this is the result of your proposal :

               

              ScreenHunter_02 Jul. 31 13.19.gif

                • Re: several links with the same table

                  I've found it !

                   

                  i need to do three left join...

                   

                   

                   

                  Interactions:

                  LOAD AccountId ,

                      Assignee_Support_Initial__c as code_operateur_initial ,

                      CreatedById as code_operateur_creation,

                      LastModifiedById as code_operateur_derniere_modification;

                   

                   

                  SQL SELECT AccountId,

                      Assignee_Support_Initial__c,

                      CreatedById,

                      LastModifiedById

                  FROM Case;

                   

                  LEFT JOIN

                  LOAD

                      FirstName & ' ' & LastName as Nom_operateur_initial ,

                      Id as code_operateur_initial

                      ;

                  SQL SELECT

                      FirstName,

                      Id,

                      LastName

                  FROM User;

                   

                   

                  LEFT JOIN

                  LOAD

                      FirstName & ' ' & LastName as Nom_operateur_creation ,

                      Id as code_operateur_creation

                      ;

                  SQL SELECT

                      FirstName,

                      Id,

                      LastName

                  FROM User;

                   

                   

                  LEFT JOIN

                  LOAD

                      FirstName & ' ' & LastName as Nom_operateur_derniere_modification ,

                      Id as code_operateur_derniere_modification

                      ;

                  SQL SELECT

                      FirstName,

                      Id,

                      LastName

                  FROM User;

                   

                   

                  and the result :

                  ScreenHunter_03 Jul. 31 13.37.gif