Re: sql question

Why? because some rows in the table relate to other rows in the same table. The classic example is the employee/supervisor relationship. Both people are employees, but if you have an employee ID and want to determine his supervisor's name, you need to do a self join.

select sup.employee_name
  from emp emp, emp sup
 where emp.supervisor_id = sup.emp_id
   and emp.emp_id = :empoyee_id

In the short amount of time I have to look at this query, and not knowing the PK, it's hard to tell what the programmer was trying to accomplish. I can't make any sense out of it. Maybe someone else can. Why would you want to join p and pa1 twice, once with an outter join and once with an inner join? Seems contradictory to me, but what do I know?

On Mon, 6 Aug 2001 14:49:21 -0500 , "Guruju,Narendra" <NGURUJU_at_cerner.com> wrote:

>Hello,
> I am looking at some of the sql scripts here at my new job and
>noticed queries like
>
>SELECT /*+ RULE */
> pa2.alias
> FROM
> person p,
> person_alias pa1,
> person_alias pa2
> WHERE
> p.person_id = pa1.person_id and
> pa1.alias = x
> and pa1.person_id = pa2.person_id
> and p.person_id = pa1.person_id(+)
>
>Is there any important reason that a table is being joined to itself. I
>have seen several queries where the same table with different aliases
>has been defined in the from clause and a self join performed on it.
>These queries were written to run against the early versions of Oracle
>7. I don't know if there is any performance gain by doing this.
>
>Thanks,
>Narendra

--
Chuck Hamilton
chuck_hamilton_at_yahoo.com

"Do not be deceived, God is not mocked; 
for whatever a man sows, this he will also
reap." (Gal 6:7 NASB)