Re: Self Join on Subquery
Date: Thu, 21 Feb 2008 05:38:24 -0800 (PST)
Message-ID: <4dd61e6b-1337-492e-920c-52ed07bfac99@o10g2000hsf.googlegroups.com>
On Feb 21, 1:32 pm, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> This is the standard example for self joins:
>
> SELECT e.last_name employee, m.last_name manager
> FROM employees e INNER JOIN employees m
> ON e.manager_id = m.employee_id;
>
> Now imagine that I want to do a self join not on a table but on a
> complicated subquery:
>
> SELECT e.last_name employee, m.last_name manager
> FROM (SELECT ... complicated subquery ...) e INNER JOIN (SELECT ...
> identical complicated subquery ...) m
> ON e.manager_id = m.employee_id;
>
> I do not like the fact that I repeat the same "complicated subquery"
> code twice in my query. Is there a possibility to do the same thing
> without including the code of the subquery twice? This would save
> time, avoid errors and would probably be faster.
>
> Thanks.
>
> Hans
http://www.psoug.org/reference/with.html
Regards
TESSELLA Michael.OShea_at_tessella.com
__/__/__/ Tessella Support Services plc __/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England __/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301www.tessella.com Registered in England No. 1466429 Received on Thu Feb 21 2008 - 07:38:24 CST