File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Oracle/OAS and the fly likes selfjoins? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "selfjoins?" Watch "selfjoins?" New topic
Author

selfjoins?

Sona Patel
Ranch Hand

Joined: Mar 30, 2009
Posts: 75
where can i find good explanation with example of oralce sql selfjoins?

Please provide some good links,books or blogs...
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

no books or links, but a simple example:


OCUP UML fundamental and ITIL foundation
youtube channel
Sona Patel
Ranch Hand

Joined: Mar 30, 2009
Posts: 75
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
Bartender

Joined: Dec 20, 2006
Posts: 2495
    
    8

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
Ranch Hand

Joined: Mar 30, 2009
Posts: 75
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
Ranch Hand

Joined: Aug 31, 2009
Posts: 61
Self joins are also a good way to get at hierarchical data.
Vinod Tiwari
Ranch Hand

Joined: Feb 06, 2008
Posts: 459
    
    1
I must recommend http://asktom.oracle.com which is a very good oracle site.


Vinod Tiwari | Twitter
 
jQuery in Action, 2nd edition
 
subject: selfjoins?