Recursive Query Problem

Question

Recursive Query Problem

nicowens

Grasshopper

Points: 21
More actions
October 10, 2009 at 6:04 pm

#135563

This is an interesting problem and I'd like to solve it using SQL2k5 CMEs instead of any of the old recursive hacks but I guess I just don't understand how exactly to go about it.
In the end, the solution needs to be wrapped up pretty into a few simple stored procedures which can be called by passing simply an Account ID as the parameter.
Design:
The design is a straight-forward ACCOUNT table which has a recursive FK. The structure is:
ACCOUNT
-----------
ACC_ID
ACC_ACC_ID
ACC_TITLE
Required Procedures:
1.) Pass in an ACC_ID value and retrieve all of the child records and their descendants.
2.) Pass in an ACC_ID value and retrieve the parent record and all the child records and their descendants.
3.) Pass in an ACC_ID value and retrieve the top-most ACC_ID in the chain, i.e. the one without a FK.
Requirements:
1.) When records are returned, I need to return them in a special order. It's in fact the way one would think intuitively that everyone would want them returned but it's the hardest thing to do. The order should show the children of children before the next child of the same level. I've indented a display below for clarity:
ACC_ID TITLE
------------------------------------------------------------------
1 Main Account
2 Child 1
4 Grandchild 1 under Child 1
5 Grandchild 2 under Child 1
6 Grandchild 3 under Child 1
3 Child 2
7 Grandchild 1 under Child 2
8 Grandchild 2 under Child 2
Newbie to CMEs, I've grabbed some samples and mostly tried to tweak them to get the right results but I haven't come up with the right combination. It just hasn't become intuitve to me yet. While the Oracle way of doing it isn't easy either, at least it was always intuitive - it made sense semantically and was easy to visualize from seeing the query.
Here's what I have so far:
---------------------------------------------------------------------------------
[font="System"]CREATE PROCEDURE GET_ACC_TREE @ACC_ID INT AS
WITH ACC_TREE (Level, Position, ReportingLevel, OrgLevel, SortKey) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.OrgLevel + 1), CAST (B.SortKey + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_TREE B ON A.ACC_ACC_ID = B.Level WHERE B.OrgLevel < 1) SELECT Level, Position FROM ACC_TREE ORDER BY SortKey ASC, OrgLevel OPTION (MAXRECURSION 4)
EXEC DBO.GET_ACC_TREE @ACC_ID = 5[/font]
---------------------------------------------------------------------------------
This actually gets the records I need and orders them perfectly, except for the life of me I can't get it to also return the main parent account as a record, without duplicating all the records for some reason.
After that, I have no idea how to start writing the expression to get the top parent when passed a grandchild or child id.
NOTE: I am building this to support a maximum recursion level of 4.
If I fiddle with this for a few more hours I will get it (I think) but I thought I'd get it documented out here with a solution so others coming down this road after me won't lose an entire day writing a recursive query.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 1

I noticed you didn't get any replies to your problem. Take a look at the first link in my signature below to see why that might be and what you can do about it.

And welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nicowens Grasshopper Points: 21 More actions · Answer 2

So, I used CME instead of CTE (Common Table Expressions) b/c I'm involved in a project in the medical training industry right now and CMEs is top of mind as (Continuing Medical Education). And also b/c the phrase Common Table Expressions isn't very descriptive, at least in terms of using it to solve recursive query problems.

I did some research and some digging to get to the bottom of how I could effectively use CTEs in a stored procedure to return the records I needed and it wasn't too hard after all. I've documented the CREATE statements you'll need below to do the following:

1.) Return the given account record and all children and all their children and so on.

2.) Given an account record, return only the children and all their children and so on. This procedure does not return the parent account passed. It can return no records if the account has no children.

3.) Given any account, this procedure returns the top-most parent in that account tree. This is widely used throughout my application since several settings for users of accounts are stored only at the top-most level of the organization.

The procedures:

1.) CREATE PROCEDURE GET_ACC_TREE @ACC_ID INT AS

WITH ACC_TREE (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_TREE B ON A.ACC_ACC_ID = B.ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_TREE ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

2.) CREATE PROCEDURE GET_ACC_CHILDREN @ACC_ID INT AS

WITH ACC_CHILDREN (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_CHILDREN B ON A.ACC_ACC_ID = B.ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_CHILDREN ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

3.) CREATE PROCEDURE GET_ACC_TOP @ACC_ID INT AS

WITH ACC_TREE (ACC_ID, ACC_TITLE, ACC_ACC_ID, ACC_LEVEL, ACC_SORT) AS (SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, 0, CAST (A.ACC_ID AS VARBINARY(900)) FROM ACC A WHERE A.ACC_ID = @ACC_ID UNION ALL SELECT A.ACC_ID, A.ACC_TITLE, A.ACC_ACC_ID, (B.ACC_LEVEL + 1), CAST (B.ACC_SORT + CAST (A.ACC_ID AS BINARY(4)) AS VARBINARY(900)) FROM ACC A INNER JOIN ACC_TREE B ON A.ACC_ID = B.ACC_ACC_ID) SELECT ACC_ID, ACC_TITLE FROM ACC_TREE WHERE ACC_ACC_ID IS NULL ORDER BY ACC_SORT ASC, ACC_LEVEL ASC OPTION (MAXRECURSION 4)

When calling one of these procedures using inline SQL, simply execute the following query:

EXEC DBO.GET_ACC_TOP @ACC_ID = 5

As an example:

rs.open "EXEC DBO.GET_ACC_TOP @ACC_ID = 5", conn

Happy programming!