• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

selfjoins?

 
Sona Patel
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
where can i find good explanation with example of oralce sql selfjoins?

Please provide some good links,books or blogs...
 
Jan Cumps
Bartender
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
no books or links, but a simple example:
 
Sona Patel
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2602
13
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Self joins are also a good way to get at hierarchical data.
 
Vinod Tiwari
Ranch Hand
Posts: 466
1
IntelliJ IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I must recommend http://asktom.oracle.com which is a very good oracle site.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic