selfjoins?

[Sona Patel]

where can i find good explanation with example of oralce sql selfjoins?

Please provide some good links,books or blogs...

[Jan Cumps]

no books or links, but a simple example:

select 
  emp.person_id as employee_id
, emp.name as employee_name
, mgr.person_id as manager_id
, mgr.name as manager_name
from 
  person emp
, person mgr
where
    emp.manager_id = mgr.person_id

[Sona Patel]

Hey Jan..

Thanks... This is the same scenario which i am working on. I am able to reach this query after trying many queries. But still not able to understand idea behind it.
Hope someone come up with good explanation.

[Jan Cumps]

Sona Patel wrote:Hey Jan..

Thanks... This is the same scenario which i am working on. I am able to reach this query after trying many queries. But still not able to understand idea behind it.
Hope someone come up with good explanation.

...
person emp
, person mgr
...

By putting an alias behind the table name you can use the table twice.
The first time we aliased the table as emp, and we can use the name emp in stead of person in the query.
The second time we aliased it as mgr, and we can use the name mgr in stead of person in the query.

So, we have joined two times the person table. The left side of the join is called emp (the employee), and the right side mgr (his manager).

...
emp.manager_id = mgr.person_id
...

We joined it so that the employee is linked to her manager.


...
, emp.name as employee_name
...
, mgr.name as manager_name
...

You can select values from the first table by putting the alias emp before the column name (emp.name), and you can select values from the second table by putting its alias mgr before the column (mgr.name).


Summary: Self joins are no different than other joins, it is just using the same table twice, and giving it each time a different alias.

Regards, Jan

[Sona Patel]

Hi Jane...

Thanks... that's so nice of you...
Apart from seeking help from ranch, i also did some google and idea is clear to me now.

[Charles Owen]

Self joins are also a good way to get at hierarchical data.

[Vinod Tiwari]

I must recommend http://asktom.oracle.com which is a very good oracle site.