Self Join Help

[#1]

Hi Team,

I have the following fields in a table :

EmpID, Name, MgrID, Salary

1, Tom, 4, 80000

2, Rose, B, 1, 8500

3, Tito, A, 4, 10000

4, Sandra, B, 1, 2000

I need to perform a self join on the above table

I have tried doing using Hierarchy function as below, but it is not working as expected.

HIERARCHY (EmployeeID, MgrID, Salary, Employee)

LOAD EmployeeId as EmployeeID,

    MgrID,

    Name as Employee,

    Salary;

LOAD * INLINE [

    EmployeeId, Name, MgrID, Salary

    1, Tom, 4, 8000

    2, Rose, 1, 8500

    3, Tito, 4, 10000

    4, Sandra, 1, 20000

   ];

OR using

LEFT JOIN Employee

LOAD

Emp.Id as Mgr.ID,

Name as "Manager Name"

RESIDENT Employee

Kindly guide me perform the same.

Thanks in advance.

[sunny_talwar]

This seems to be working:

Table:

LOAD * Inline [

EmpID, Name, MgrID, Salary

1, Tom, 4, 80000

2, Rose, 1, 8500

3, Tito, 4, 10000

4, Sandra, 1, 2000

];

Left Join(Table)

LOAD EmpID as MgrID,

  Name as ManagerName

Resident Table;

Output:

[tresesco]

Not really sure what you are trying to get. May be like you said:

Employee:

LOAD * INLINE [

    EmployeeId, Name, MgrID, Salary

    1, Tom, 4, 8000

    2, Rose, 1, 8500

    3, Tito, 4, 10000

    4, Sandra, 1, 20000

   ];

LEFT JOIN Employee

LOAD

EmployeeId as MgrID,

Name as "Manager Name"

RESIDENT Employee;

Did you try that?

[avinashelite]

Hi Robin,

what's your exact requirement ?

try like this:

Hierarchy (EmployeeId, MgrID, Name,,Name,Hierarchy,'\',TreeDepth)

LOAD * INLINE [

    EmployeeId, Name, MgrID, Salary

    1, Tom, 4, 8000

    2, Rose, 1, 8500

    3, Tito, 4, 10000

    4, Sandra, 1, 20000

   ];

try to understand the Hierarchy Parameters and its order

OR try like this

Employee:

LOAD * INLINE [

    EmployeeId, Name, MgrID, Salary

    1, Tom, 4, 8000

    2, Rose, 1, 8500

    3, Tito, 4, 10000

    4, Sandra, 1, 20000

   ];

LOAD MgrID as EmployeeId,

          Name as Manager_Name

resident

Employee;

[jonathandienst]

Perhaps because your data has a circular relationship - Tom's manager is Sandra. Sandra's manager is Tom... Sunny showed you can load this, but is it correct?