Self Join on Subquery

#1

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

#2

On Feb 21, 1:32*pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

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 (DOT) 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

#3

On Feb 21, 1:32*pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

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 (DOT) 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

#4

On Feb 21, 1:32*pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

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 (DOT) 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

#5

On Feb 21, 1:32*pm, Hans Mayr <mayr1... (AT) gmx (DOT) de> wrote:

Quote:

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 (DOT) 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