Abhishek Jain Abhishek Jain -4 years ago 43
SQL Question

SQL Union/Join Query

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 hierarchy for a user. Can somebody help?

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

Update

I managed to solve 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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download