Table X Left Join with itself

[sculptorlv]

Hello, please help with such situation:

I have table X with columns:

ID     Name     Friend_ID

1     John         

2     Alex    

3     MIchel     1

4     Daniel     2

Where Friend_ID is the same values as ID (it showns the same row)

I need to make an SQL quary in order to get next info:

Name     Friend_Name

John

Alex

Michel     John

Daniel     Alex

How can I do it?

Thank you in advance.

[sculptorlv]

Just cheked... data is ok.

This formula works only, if Friend_ID and ID is equal.

[sunny_talwar]

Right exactly, this will only work when Friend_ID = ID. Are you doing some other match?

[sculptorlv]

No... I mean:

IF

ID Name Friend_ID

3   Alex   3

Then it will work.. otherwise, IF

ID   Name   Friend_ID

1     Nick     2

2     John  

The result will be:

1 Nick 2   -

2 John    -    -

[sculptorlv]

How does system know, on which field must JOIN be made?

Left Join (Table_1)

LOAD

//Alternative_Contract_TS_ID AS Contract_TS_ID,

Contract_TS_ID AS Alternative_Contract_TS_ID,

Contract_Number AS Alternative_Contract_Number

Resident Table_1;

[marcus_sommer]

The join happens to the same field-names. Beside them have you considered not to join the data and simply just to use two associated tables?

- Marcus

[sculptorlv]

yep, I was thinking about it .. but then all other calculation will be very difficult

[sculptorlv]

I used this construction .. and seems that it works, BUT:

As soon, as I use WHERE statment -  connection became lost.

My construction is:

LOAD

Contract_TS_ID,

Contract_Number,

Alternative_Contract_TS_ID;

SQL SELECT

TABLE_Contracts_TS.ID AS Contract_TS_ID,

...

FROM "TS_Live".dbo."tbl_Contract" AS TABLE_Contracts_TS

LEFT JOIN ......TABLE_ANOTHER;

// WHRE TABLE_ANOTHER.Column1 > 0; This corrupt all calculations

Mapping:

Mapping

LOAD

Contract_TS_ID,

Contract_Number

Resident Table_1;

FinalTable:

LOAD *,

  ApplyMap('Mapping', Alternative_Contract_TS_ID, Null()) as Alternative_Contract_Number

Resident Table_1;

DROP Table Table_1;

[Anonymous]

I Use this exp

Contact:

Load ContactID,

     CountryID,

     DepartmentID ,    

     UniversityID,

     FacultyID,

     ContactIDCompany,

     ContactTypeID,

     ContactName,

     FirstName,

     RTrim(ContactName)  &' '& RTrim(MiddleName) &' '& RTrim(FirstName)  As Contact_FullName,

     MiddleName,

     OldName,

     UserName,

     UserPassword,

     UserGroupID,

     If(Active=-1,'Y','N') As [Contact_Active],

     RoleID,

     OrgDeptID As [Contact OrgDeptID],

     PrefixID,

     JobTitle,

     WebPage,

     OldNum,

     Remark As [Contact Remark],

     ExchangeMailBoxName,

     DisplayResponsible,

     ContactIDResponsible

    From Contact

Left Join

Load   ContactID ,

ContactIDCompany

From Contact

Left Join

Load   ContactID As ContactIDCompany     ,

       ContactName &' '& RTrim(FirstName) &' '& RTrim(MiddleName)      As [Contact_Company]     

From Contact