locked
Recursive query, parent-child relations mixed in a table RRS feed

  • Question

  • Hi,

    I've got a problem writing recursive query to give me all values from the the table. If you look (run) at the example below (taken from this forum) you will see that the 'Name 4' line is not included in the results because  CODE and PARENT should be the other way around 4->2 NOT 2->4, but this is the situation I've got in my table. Is there any easy way to write/sort this query to return me all 5 records in correct hierarchy? It's doing my headache :(. Thanks

    USE tempdb;
    GO

    DECLARE @t TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );

    INSERT INTO @t VALUES(1,      'Name1',   NULL);
    INSERT INTO @t VALUES(2,      'Name2',   1);
    INSERT INTO @t VALUES(3,      'Name3',   NULL);
    INSERT INTO @t VALUES(2,      'Name4',   4);
    INSERT INTO @t VALUES(5,      'Name5',   3);

    WITH hierarchy
    AS
    (
    SELECT Code, Parent, [Name], 0 AS lvl, CAST('\' + LTRIM(Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t
    WHERE Parent IS NULL

    UNION ALL

    SELECT c.Code, c.Parent, c.[Name], p.lvl + 1 AS lvl, p.[path] + CAST(LTRIM(c.Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t AS c INNER JOIN hierarchy AS p ON c.Parent = p.Code
    )
    SELECT parent, code as child,   
    (REPLICATE(SPACE(1), lvl * 4)) + [Name]
    FROM hierarchy
    ORDER BY [path];
    GO


    Saturday, December 29, 2012 11:21 PM

Answers

  • then it is a guessing game for you.

    Lets see if this helps , just interchanging the code and parent code if duplicate code if found

    USE tempdb;
    GO
    
    DECLARE @t TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );
    
    INSERT INTO @t VALUES(1,      'Name1',   NULL);
    INSERT INTO @t VALUES(2,      'Name2',   1);
    INSERT INTO @t VALUES(3,      'Name3',   NULL);
    INSERT INTO @t VALUES(2,      'Name4',   4);
    INSERT INTO @t VALUES(5,      'Name5',   3);
    
    WITH hierarchy
    AS
    (
    SELECT Code,Name,Parent, ROW_NUMBER() OVER(partition by Code order by code,parent) RN  FROM @t 
    ),
    hierarchy_Corrected
    as(SELECT case when RN>1 then Parent ELSE Code END Code ,Name, CASE WHEN RN>1 THEN Code ELSE Parent  END Parent FROM hierarchy)
    select * from hierarchy_Corrected
    Order by Code,Name

    Regards
    satheesh

    • Marked as answer by zondan Sunday, December 30, 2012 10:32 PM
    • Unmarked as answer by Kalman Toth Sunday, December 30, 2012 10:58 PM
    • Proposed as answer by Naomi N Monday, December 31, 2012 12:15 AM
    • Marked as answer by Kalman Toth Monday, December 31, 2012 12:34 AM
    Sunday, December 30, 2012 5:06 PM

All replies

  • Populate the following table with a PRIMARY KEY constraint correctly and post code. Thanks.

    USE tempdb;
     GO
     
    DECLARE @t TABLE (
     Code INT NOT NULL  PRIMARY KEY, -- UNIQUE CLUSTERED,
     [Name] VARCHAR(25),
     Parent INT NULL
     );
     


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    • Edited by Kalman Toth Sunday, December 30, 2012 5:13 PM
    Sunday, December 30, 2012 12:04 AM
  • HI

    See the  Recursive UNION article ,Details is described here nicely .Please go through the article

    http://sqlanywhere.blogspot.com/2012/04/example-recursive-union.html


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Sunday, December 30, 2012 7:14 AM
  • hmmmm, I see you have orphans in the table, is this problem with design or the data?

    See if this helps

    USE tempdb;
    GO
    
    DECLARE @t TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );
    
    INSERT INTO @t VALUES(1,      'Name1',   NULL);
    INSERT INTO @t VALUES(2,      'Name2',   1);
    INSERT INTO @t VALUES(3,      'Name3',   NULL);
    INSERT INTO @t VALUES(2,      'Name4',   4);
    INSERT INTO @t VALUES(5,      'Name5',   3);
    
    WITH hierarchy
    AS
    (
    SELECT Code, Parent, [Name], 0 AS lvl, CAST('\' + LTRIM(Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t
    WHERE Parent IS NULL
    union  all
    select code,parent,[Name], 0,Name from @t where Parent not in(select Code from @t)
    UNION ALL
    SELECT c.Code, c.Parent, c.[Name], p.lvl + 1 AS lvl, p.[path] + CAST(LTRIM(c.Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t AS c INNER JOIN hierarchy AS p ON c.Parent = p.Code
    
    
    )
    SELECT parent, code as child,   
    (REPLICATE(SPACE(1), lvl * 4)) + [Name]
    FROM hierarchy
    ORDER BY [path];
    GO
    Regards
    Satheesh
    Sunday, December 30, 2012 7:32 AM
  • So if I read between the lines correctly, in real life you have a table with incorrect parent-code relations, and you need a query that can identify the incorrect relations and produce a result as if the data had been entered correctly. The table you have posted is just an example of the situation, right?

    It sounds non-trivial to me, because that code will ultimately have to make guesses.

    It seems me that the best strategy would be to track down which rows where Code/Parent should be swapped. In this case we can tell that one of 2/1 and 2/4 are wrong, because they violate the constraint. Since there is a row with Code = 1 but no row with Code = 4, we can conclude that 2/4 is wrong and should be 4/2. But I suspect that in the real table, it may be not that simple. At least I would like to see more data before I start writing queries. And my assumption is that we should correct the bad data, before we start to write queries against it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 30, 2012 11:11 AM
  • Hi Satheesh,

    Thanks for the code. Ideally 'Name 4' should be the child of 'Name 2'. As many of you noticed this is the table data and design problem, I would have to guess to get it right. No chance I can get this table changed as this is populated by some 3rd party software.

    The table contains products ID and related products ID, but sometimes child product is connected to parent product the other way around. I guess I'll have to live with this for the time being. I'll use your suggestions and play with the code.

    If I come up with any solution I'll post it. Thanks for help and all your suggestions.

    Daniel

    Sunday, December 30, 2012 4:20 PM
  • Thanks Erland. You're totally right should be 4/2, but I cannot change the data in the table. I'll experiment with the code and eep you updated. Daniel
    Sunday, December 30, 2012 4:39 PM
  • The adjacency list model for trees has serious problems It is not normalized and you cannot easily write data integrity constraints. For example, you do nothing to prevent two root nodes, or someone from being his own boss, or the data having cycles and orphans. 

    Google the  Nested Sets model and avoid these problems. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Kalman Toth Sunday, December 30, 2012 5:13 PM
    Sunday, December 30, 2012 4:51 PM
  • then it is a guessing game for you.

    Lets see if this helps , just interchanging the code and parent code if duplicate code if found

    USE tempdb;
    GO
    
    DECLARE @t TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );
    
    INSERT INTO @t VALUES(1,      'Name1',   NULL);
    INSERT INTO @t VALUES(2,      'Name2',   1);
    INSERT INTO @t VALUES(3,      'Name3',   NULL);
    INSERT INTO @t VALUES(2,      'Name4',   4);
    INSERT INTO @t VALUES(5,      'Name5',   3);
    
    WITH hierarchy
    AS
    (
    SELECT Code,Name,Parent, ROW_NUMBER() OVER(partition by Code order by code,parent) RN  FROM @t 
    ),
    hierarchy_Corrected
    as(SELECT case when RN>1 then Parent ELSE Code END Code ,Name, CASE WHEN RN>1 THEN Code ELSE Parent  END Parent FROM hierarchy)
    select * from hierarchy_Corrected
    Order by Code,Name

    Regards
    satheesh

    • Marked as answer by zondan Sunday, December 30, 2012 10:32 PM
    • Unmarked as answer by Kalman Toth Sunday, December 30, 2012 10:58 PM
    • Proposed as answer by Naomi N Monday, December 31, 2012 12:15 AM
    • Marked as answer by Kalman Toth Monday, December 31, 2012 12:34 AM
    Sunday, December 30, 2012 5:06 PM
  • T-SQL recursive sproc scripts demonstrate the creation of an Organizational Chart for AdventureWorks Company based on the Employee Parent-Child (manager-employee) table.

    http://www.sqlusa.com/bestpractices/training/scripts/recursivecte/

    Sunday, December 30, 2012 6:18 PM
  • Hi Kalman,

    Neither child nor parent are primary key, there is surrogate autoincrement ID field in the table. In the query above I commented out the PRIMARY KEY, otherwise it wouldn't let me to show the situation I've got in my table. Thaks for response and your help.

    Sunday, December 30, 2012 7:52 PM
  • Hi Satheesh,

    Thanks for this query. Using this query I can create a proper parent child structure (see additional @t2 in my query) and then using standard recursive sql can create correct hierarchy. I quickly put together an example based on your query and it seems to return correct results. I'll try to use it on the real table. I hope it will help me to create report I was requested to make. You need to be a really smart guy to come up with solutions like this.

    This is the modified query:

    USE tempdb;
    GO

    DECLARE @t TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );

    DECLARE @t2 TABLE (
    Code INT NOT NULL, -- UNIQUE CLUSTERED,
    [Name] VARCHAR(25),
    Parent INT NULL
    );

    INSERT INTO @t VALUES(1,      'Name1',   NULL);
    INSERT INTO @t VALUES(2,      'Name2',   1);
    INSERT INTO @t VALUES(3,      'Name3',   NULL);
    INSERT INTO @t VALUES(2,      'Name4',   4);
    INSERT INTO @t VALUES(5,      'Name5',   3);

    WITH hierarchy
    AS
    (
    SELECT Code,Name,Parent, ROW_NUMBER() OVER(partition by Code order by code,parent) RN  FROM @t
    ),
    hierarchy_Corrected
    as (SELECT case when RN>1 then Parent ELSE Code END Code ,Name, CASE WHEN RN>1 THEN Code ELSE Parent  END Parent FROM hierarchy)
    insert into @t2
    select *
    from hierarchy_Corrected
    Order by Code,Name;

    --select * from @t2;


    WITH hierarchy
    AS
    (
    SELECT Code, Parent, [Name], 0 AS lvl, CAST('\' + LTRIM(Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t2
    WHERE Parent IS NULL

    UNION ALL

    SELECT c.Code, c.Parent, c.[Name], p.lvl + 1 AS lvl, p.[path] + CAST(LTRIM(c.Code) + '\' AS VARCHAR(MAX)) AS [path]
    FROM @t2 AS c INNER JOIN hierarchy AS p ON c.Parent = p.Code
    )
    SELECT parent, code as child,   
    (REPLICATE(SPACE(1), lvl * 4)) + [Name] as [hierarchy]
    FROM hierarchy
    ORDER BY [path];
    GO

    Thank you.

    Daniel

    Sunday, December 30, 2012 10:32 PM
  • Cool it, Kalman. Zondan has already told us that his table is a mess. There is no reason to spank further for that. If he think Satheesh's query solves the problem, that's the answer, and if Zondan marked it as an answer it is. Do you really think you know Zondan's problem better than he does himself? To me it seems that you have not even understood it...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 30, 2012 11:51 PM