Combining columns in a recursive query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Combining columns in a recursive query

Author

Topic

Red.Fox-1
Starting Member

2 Posts

Posted - 2008-12-02 : 11:17:28

OK, I know there's a simple solution to this, but my brain has shut down.

I have a table (RCs) with an organization's Dept. codes (RC) and their reporting relationships, as follows:

RC Report_To
1000 2500
1001 2500
1002 2500
1500 3000
1501 3000

So I need a query that takes an RC as an argument (RC_To_Search), and does a recursive search of all RC's that report to it. So, if I pass '2500', it should return 2500, 1000, 1001 and 1002. If I pass '3000', I get back 3000, 1500 and 1501.

Now I wrote a recursive query that WORKS (below). You'll see that I used aliases (a, b, c, d, e) for the same table to be able to compare it to itself.

SELECT a.RC, b.RC, c.RC, d.RC, e.RC
FROM ((((SELECT * FROM RCs WHERE RC = RC_To_Search ) AS a
LEFT JOIN RCs AS b ON b.Reports_To = a.RC)
LEFT JOIN RCs AS c ON c.Reports_To = b.RC)
LEFT JOIN RCs AS d ON d.Reports_To = c.RC)
LEFT JOIN RCs AS e ON e.Reports_To = d.RC

The problem is that the results are returned in FIVE colomns, as follows (I only used 2 levels to simplify):

a.RC b.RC c.RC d.RC e.RC
2500 1000 null null null
2500 1001 null null null
2500 1002 null null null

What I need is to modify the above select so that all of the RC's are returned in ONE column, so that it looks like this:

RC_List
2500
1000
1001
1002

Sound simple? I thought so too. Can anyone help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:36:15

Use IIF to get the non-NULL values.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-02 : 11:36:54

Or simpler

SELECT "" & a.RC & b.RC & c.RC & d.RC & e.RC

E 12°55'05.63"
N 56°04'39.26"

Red.Fox-1
Starting Member

2 Posts

Posted - 2008-12-02 : 12:20:04

Yeah, I tried that. This gives me one column with concatenated results in each field.

RC_List
25001000
25001001
25001002

but I need one RC per field

RC_List
2500
1000
1001
1002

IIF might work, but it would have to be nested 5 times, wouldn't it?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-12-03 : 14:36:59

You'd need to use UNION all to put together the results:

select *
from
(
select RC as Parent, RC as Child
from RCs

union all

select a.RC, b.RC
FROM RCs a LEFT JOIN RCs b ON b.Reports_To = a.RC

union all

select a.RC, c.RC
FROM RCs a
LEFT JOIN RCs b ON b.Reports_To = a.RC
LEFT JOIN RCs c ON c.Reports_To = b.RC

union all

select a.RC, d.RC
FROM RCs a
LEFT JOIN RCs b ON b.Reports_To = a.RC
LEFT JOIN RCs c ON c.Reports_To = b.RC
LEFT JOIN RCs d ON d.Reports_To = c.RC
)
x
where Parent = RC_To_Search

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources