Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recursive Query Problem Expand / Collapse
Author
Message
Posted Saturday, October 10, 2009 6:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 10:25 PM
Points: 3, Visits: 8
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:

---------------------------------------------------------------------------------
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

---------------------------------------------------------------------------------

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.
Post #801248
Posted Tuesday, October 13, 2009 7:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 36,995, Visits: 31,520
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #802482
Posted Wednesday, October 14, 2009 10:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 10:25 PM
Points: 3, Visits: 8
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!
Post #802940
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse