Geek asks and answers

How can I get the parents of all parents as columns for a child in a pivot table?

I have a table with columns like

entityID, entityName, parentID

How can I write a query to return all parent levels for an object to return something like

childentityname, parentlevel1name, parentlevel2name, parentLevel3name and so on

I am in no way a SQL ninja. Is it possible? If so, how?

I am using a Microsoft SQL Server database.

+3
source share
3 answers

Recursive CTE is what you need to look here (EDIT: Only in SQL SERVER 2005 +)

Something along the lines of:

WITH recurse_cte (entityID,entityName, parentID, Level)
AS
(
-- Anchor member definition
    SELECT e.entityID,e.entityName, e.parentID,
        0 AS Level
    FROM self_joined AS e
        UNION ALL
-- Recursive member definition
    SELECT e.entityID,e.entityName, e.parentID,
        Level + 1
     FROM self_joined AS e
    INNER JOIN recurse_cte AS cte
        ON e.entityID = cte.parentID
)

select * from recurse_cte
+3
source

In postgres, this is exactly what it is for WITH RECURSIVE. You probably don't need to do much more than change the column names from ( here) .

, OP DB , , . , . , . SQL-, .

0
SELECT 
  'accounts'.'id' AS id_0,
  'accounts'.'child_id' AS child_id_0, 
  'child_accounts_1'.'id' AS id_1, 
  'child_accounts_1'.'child_id' AS child_id_1, 
  'child_accounts_2'.'id' AS id_2, 
  'child_accounts_2'.'child_id' AS child_id_2, 
  'child_accounts_3'.'id' AS id_3, 
  'child_accounts_3'.'child_id' AS child_id_3, 
  'child_accounts_4'.'id' AS id_4, 
  'child_accounts_4'.'child_id' AS child_id_4
FROM 
  'accounts' 
LEFT OUTER JOIN 'accounts' 'child_accounts_1'
  ON 'child_accounts_1'.'id' = 'accounts'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_2'
  ON 'child_accounts_2'.'id' = 'child_accounts_1'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_3'
  ON 'child_accounts_3'.'id' = 'child_accounts_2'.'child_id'
LEFT OUTER JOIN 'accounts' 'child_accounts_4'
  ON 'child_accounts_4'.'id' = 'child_accounts_3'.'child_id'
WHERE 'accounts'.'id' = 56

, , - .

accounts negative_overflow_account_id, . "id" "negative_overflow_id" 5 .

, MAX_OVERFLOW, "5", /, .

Basically, my use case was to make sure that someone does not set up an infinitely circular loop, so if he reaches level 5, then the user receives an error telling them that they cannot set it so deep. And if any of the levels refers to the upper level or to one of the previous levels, then an error is also generated that indicates circular recursion (which may lead to the application crashing later if it is allowed to continue).

EDIT: I shortened the names. nobody wants to see my stupid ridiculous naming convention for this stupid table;)

0
source

More articles:


All Articles