several links with the same table

[#1]

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

[#2]

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;

[#3]

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 :

[#4 · Accepted solution]

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 :