I need Help for Recursive CTE

Amita Acharya

Amita Acharya

@amita-acharya-SPGOT7 • Oct 22, 2024
Hello ,

I want to create a query where in there are following columns
Parent Id,Child Id ,Name,Levels
..

I have written following query to it.
WITH RESULT (PARENT,CHILD,TNAME,LEVEL)
AS
(
--PARENT
SELECT E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,E.NAME,0 AS LEVEL
FROM RPT_SYN_M_GENERAL AS E WHERE E.PARENT_GENERAL_KEY IS NULL

UNION ALL

--CHILD
SELECT E.PARENT_GENERAL_KEY,E.M_GENERAL_KEY,e.NAME,LEVEL +1
FROM RPT_SYN_M_GENERAL AS E INNER JOIN RESULT AS D
ON E.PARENT_GENERAL_KEY=D.CHILD
)

SELECT d.PARENT,d.CHILD,d.TNAME,LEVEL FROM RESULT as d
order by PARENT,CHILD

....
the output i am getting is correct till anchor query
but further its not iterating at every level.

I have attached the output i am getting.
my desired output is WHAT AGAIN I AM ATTACHING
PLEASE HELP ME WITH IT.

THANKS & REGARDS,
AMITA ACHARYA
Like 0 Replies 3

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

Sign up Log in

CrazyEngineers powered by Jatra Community Platform

  • Amita Acharya

    Amita Acharya

    @amita-acharya-SPGOT7 • Apr 22, 2012

    i AM HEREBY ATTACHING THE KIND OF OUTPUT I NEED..
    PLEASE HELP ME WITH IT.

    THANKS & REGARDS,
    AMITA ACHARYA
    0
  • Ankita Katdare

    Ankita Katdare

    @abrakadabra • Apr 29, 2012

    #-Link-Snipped-# You could help her out here. ☕
    0
  • PraveenKumar Purushothaman

    PraveenKumar Purushothaman

    @praveenkumar-66Ze92 • Apr 29, 2012

    AbraKaDabra
    #-Link-Snipped-# You could help her out here. ☕
    Thanks #-Link-Snipped-#! 😀

    Hi Amita,
    I guess you would like to find the depth of the child from the parent. We cannot do it in straight forward way, but we can do using Closure table method. Consider this example:
    CREATE TABLE Categories (
  node_id INT AUTO_INCREMENT PRIMARY KEY ,
  label VARCHAR(40) NOT NULL
);
    CREATE TABLE Closure (
   ancestor INT NOT NULL,
   descendant INT NOT NULL,
   PRIMARY KEY (ancestor, descendant),
   FOREIGN KEY (ancestor) REFERENCES Nodes(node),
   FOREIGN KEY (descendant) REFERENCES Nodes(node)
);
    SELECT count( Categories.node_id ) AS depth
FROM Closure AS Des
     JOIN Closure AS Anc ON (Anc.descendant = Des.descendant)
     JOIN Categories ON ( Categories.node = Anc.ancestor)
WHERE Des.ancestor = 1 AND Des.descendant != Des.ancestor
GROUP BY Des.descendant;
    We have used a closure table here. You can find more about closure tables here: #-Link-Snipped-#

    Try this and let us know the results... 😀
    0