SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recursive CTE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

craiggsmith
Starting Member

USA
2 Posts

Posted - 04/05/2013 :  19:31:09  Show Profile  Reply with Quote
I'm new to SQL Server (longtime Oracle user) and one of my first tasks is to create a recursive query. After some online searching I've come up with the following, but it doesn't work.

For this example's sake consider a table RELATIONSHIP with 2 columns, CHILD and PARENT, and the following data (columns separated by comma):
1,0
2,1
3,2
4,3
5,4

I want the output to be each child with its topmost parent, i.e in this case they all roll up to 0. Here's the query (minus the final select):
WITH CTE(CHILD, PARENT, Level)
AS (
SELECT CHILD, PARENT, 0 AS Level
FROM RELATIONSHIP
WHERE PARENT = 0
UNION ALL
SELECT CHILD, PARENT, Level + 1 AS Level
FROM RELATIONSHIP R
INNER JOIN CTE C
ON R.PARENT = C.CHILD
)

What I'm getting is:
1,0,0
2,1,1
3,2,2
4,3,3
5,4,4

In other words, I get the correct number of levels to the top but the parent listed is only the immediate parent, not the top one.

What am I doing wrong? I assume it's something simple but I've tried all sorts of variations to no avail.

Thanks very much.

Edited by - craiggsmith on 04/05/2013 19:31:44

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 04/07/2013 :  11:32:35  Show Profile  Reply with Quote
Hi Craig,

I believe the following will do as you ask.
--=============================================================================
--      Create the test table.
--      Nothing in this section has to do with the solution of the problem.
--      We're just creating test data here.  You'll get faster/better answers
--      in the future if you provide the data for future posts in a similar
--      manner.  It would also be a great help if you posted actual working,
--      properly aliased code the produced your output instead of the snippet
--      that you posted ;-)
--=============================================================================
--===== Since we're dropping a test table, do this in a nice, safe place that
     -- everyone has so we don't accidently drop a real table.
    USE tempdb
;
--===== Suppress the auto-display of rowcounts to make the output prettier
     -- and to prevent returning false errors (as rowcounts) to the GUI
     -- if one is involved later on.
    SET NOCOUNT ON
;
--===== Conditionally drop the test table to make reruns in SSMS easier.
     -- (This is not a part of the solution.  We're just creating a test table here)
     IF OBJECT_ID('tempdb.dbo.RelationShip','U') IS NOT NULL
        DROP TABLE tempdb.dbo.RelationShip
;
--===== Create the test table with some indexing.
     -- (This is not a part of the solution.  We're just creating a test table here)
 CREATE TABLE tempdb.dbo.RelationShip
        (
        Child  INT PRIMARY KEY CLUSTERED,
        Parent INT
        )
;
--===== Populate the test table.
     -- (This is not a part of the solution.  We're just creating a test table here)
 INSERT INTO tempdb.dbo.RelationShip
        (Child, Parent)
 SELECT 1,0 UNION ALL
 SELECT 2,1 UNION ALL
 SELECT 3,2 UNION ALL
 SELECT 4,3 UNION ALL
 SELECT 5,4
;
GO
--=============================================================================
--      I assume that you're going to want to make the code reusable.  With
--      that in mind, I suggest the use of an INLINE TABLE VALUED FUNCTION
--      Like the following.  Note that we're still in TempDB but I kept the
--      3 part naming convention to prevent any accidents.  You should drop the
--      3 part naming for your production code.
--      Do notice the proper aliasing which prevents ambiguity errors.
--=============================================================================
--===== Conditionally drop the function to make reruns in SSMS easier.
     -- This drop wouldn't normally go into production
     IF OBJECT_ID('tempdb.dbo.GetDownlinePlusTopParent','IF') IS NOT NULL
        DROP FUNCTION dbo.GetDownlinePlusTopParent
;
GO
 CREATE FUNCTION dbo.GetDownlinePlusTopParent
        (@Child INT)
RETURNS TABLE -- WITH SCHEMABINDING --(Uncomment WITH SCHEMABINDING for production)
     AS 
 RETURN
   WITH CTE(Child, Parent, TopParent, Level) AS
        (
         SELECT Child, Parent, TopParent = Parent, Level = 0
           FROM dbo.Relationship
          WHERE Child = @Child
          UNION ALL
         SELECT r.Child, r.Parent, c.TopParent , Level = c.Level + 1
           FROM dbo.Relationship r
          INNER JOIN CTE c
             ON r.Parent = c.Child
        )
         SELECT Child, Parent, TopParent, Level
           FROM CTE
;
GO
--=============================================================================
--      Example usage:
--=============================================================================
--===== Suppress the auto-display of rowcounts to make the output prettier
     -- and to prevent returning false errors (as rowcounts) to the GUI
     -- if one is involved later on.
    SET NOCOUNT ON
;
--===== Do two different calls to the function to show how it's used.
 SELECT Child, Parent, TopParent, Level
   FROM dbo.GetDownlinePlusTopParent(1)
;
 SELECT Child, Parent, TopParent, Level
   FROM dbo.GetDownlinePlusTopParent(3)
;

Here are the results...
Child       Parent      TopParent   Level
----------- ----------- ----------- -----------
1           0           0           0
2           1           0           1
3           2           0           2
4           3           0           3
5           4           0           4

Child       Parent      TopParent   Level
----------- ----------- ----------- -----------
3           2           2           0
4           3           2           1
5           4           2           2



Because of the request to "smear" the top level parent, I believe I know where the larger picture may be headed. Please see the following article for how to write some very high performance code to solve what I believe is the direction that you may need to head in.

http://www.sqlservercentral.com/articles/T-SQL/94570/

Let us know how it all works out for you.

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

When writing schedules, keep the following in mind:
"If you want it real bad, that's the way you'll likely get it."

Edited by - Jeff Moden on 04/07/2013 11:41:37
Go to Top of Page

craiggsmith
Starting Member

USA
2 Posts

Posted - 04/08/2013 :  11:04:38  Show Profile  Reply with Quote
Thank you very very much for the excellent explanation. I see the difference and how it works -- in the select after the union I just needed to select the parent from the CTE instead. This is different than all the other examples I've seen out there.

I apologize for the format of my post; I was hesitant to use a real example for confidentiality reasons, although now I realize it's just a common off-the-shelf application and it wouldn't have been an issue. And I figured the error would probably be obvious without anyone actually having to execute anything. But if necessary I will include proper executable code in the future. Thanks for understanding.

I will take a look at your article and see if it helps take me where I need to go. Thanks again!


- Craig
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000