Self join in IDT Universe

[former_member588739]

I'm preparing a tutorial for internal purpose and want to double check below setup for self join in IDT.

Sample database has staff table with employee ID + Manager's id. Relation is defined as following:

staff_id (0 or N) to manager_id (0 or 1).

Is below setup correct for this config?

[TammyPowlas]

Marcin - thank you for your question.

For faster response, I changed the main tag to Semantic Layer

[Joe_Peters]

Is this really a self-join or are you joining a table with an alias of itself?

Do you really want to filter the table so that only records in which the manager_id is equal to staff_id are included?

[former_member588739]

joe.peters2 - I want to see staff who has a manager and 'top manager' - without manager assigned. I did it by doing alias join and setting outer on staff table as suggested by mhmohammed

[mhmohammed · Accepted solution]

Hi Marcin,

You should create an Alias of mszpot_staffs, and name it as (probably) mszpot_mgrs, and create join as below, and check Outer join on msgpot_staffs table.

mszpot_mgrs.staff_id = mszpot_staffs.staff_id.

Thanks,
Mahboob Mohammed

[former_member588739 · Accepted solution]

So I understand IDT doesn't allow to do an actual self join but only via alias, correct?

[amitrathi239]

It's look correct.

[mhmohammed]

Hi Marcin,

Actually, Self Join (Self Restricting Join) is a term used in IDT in a different way. If you want to filter a column in a table in the Data Foundation Layer, check out the details of Self Restricting Join in this page.

https://www.wisdomjobs.com/e-university/sap-bo-tutorial-252/defining-specific-types-of-joins-4263.ht...

Apart from that, not just in IDT or UDT, even when you write free hand sql in a database client tool like Toad or SQL Developer or SQL Server Management Studio, when you want to use a table for 2 different purposes (and you want to join it to itself), you'll have to create an Alias, and this Employee and Manager relation is a classic example.

In IDT you create an Alias using the GUI feature, but when you write SQL, you need to write the SQL as below, where mgr is an alias of emp table, created during runtime, and empid of alias table is joined to mgrid of emp table. The alias table MGR is used to get info about an Employee's Manager. Hope that helps.

select emp.name, mgr.name
from emp
left join emp mgr on (emp.mgrid = mgr.empid)

Thanks,
Mahboob Mohammed

[mhmohammed]

Hi Marcin,

If an answer has helped you, please mark it as Accepted, for 2 reasons. 1. It separates Unanswered and Answered questions, which'll help people. 2. I'll get points for it.

Thanks,
Mahboob Mohammed