Self Join on Subquery

From: Hans Mayr <mayr1972_at_gmx.de>
Date: Thu, 21 Feb 2008 05:32:16 -0800 (PST)
Message-ID: <c7b8ae05-f422-4c83-831f-1d2ae66060ae@d5g2000hsc.googlegroups.com>


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 Received on Thu Feb 21 2008 - 07:32:16 CST

Original text of this message