SQL Union/Join Query

Question

I have a user profile table with columns User Name, manager and many other fields, for example amount..

Example Records:-

User    Manager  Amount
A       B       100
x       y       200
B       C       300
M       N       800
C       D       500
P       Q       1000
D       E       1000

I am trying to get the result as below:-

User    Manager Amount
A       B       100
B       C       300
C       D       500
D       E       1000

Basically, I just want to show the results in cascading manner so that all the rows are fetched until it finds all managers in the heirarchy for a user. Can somebody help?

============

Update

I managed to solved the problem using the below query:-

WITH rec(c1, c2) AS (SELECT c1, c2 FROM table WHERE c2 = 'A' UNION ALL SELECT table.c1, table.c2 FROM table, rec WHERE table.c2 = rec.c2) SELECT c1, c2 FROM rec

Thanks for the help. Abhi.

Answer 1

there is no "standard" sql for doing that, However most databases extend SQL to have these options.

In Oracle you can do something like:

SELECT USER, Manager, Amount FROM employees CONNECT BY PRIOR USER = Manager;

please see some examples in: http://www.ibm.com/developerworks/data/library/techarticle/dm-0510rielau/ for db2 examples