Re: Self Join on Subquery

From: Michael O'Shea <michael.oshea_at_tessella.com>
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-553301
www.tessella.com Registered in England No. 1466429 Received on Thu Feb 21 2008 - 07:38:24 CST

Original text of this message