This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: 2491
    
    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: 2491
    
    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
 
Don't get me started about those stupid light bulbs.
 
subject: selfjoins?
 
Similar Threads
Looking for a position in java, J2ee, XML, Spring, Ajax, Hibernate
Laloo become a PM
RequestDispatcher
Cleared SCWCD with 78% today
Regarding developing and testing EJB 3.0