CrazyEngineers Logo

I need Help for Recursive CTE

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

Replies

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

    THANKS & REGARDS,
    AMITA ACHARYA
  • Ankita Katdare
    Ankita Katdare
    #-Link-Snipped-# You could help her out here. ☕
  • PraveenKumar Purushothaman
    PraveenKumar Purushothaman
    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... 😀

You are reading an archived discussion.

Related Posts

Data structure: Identify the correct option

Consider the declaration below: typedef char *Str_typed; #define Str_defined char* Str_typed s1, s2; Str_defined s3, s4; Which one of the following statements is correct? I. s1, s2, s3 and s4...

INTRODUCTION- AMITA ACHARYA

Name: AMITA ACHARYA *Engineering Trade: MCA Location: MUMBAI *Occupation: (Student) Work Experience: (if applicable) *Hobbies & Interests: LISTENING MUSIC *Aim in life: a SUCCESSFUL PERSON *I joined CrazyEngineers because: MANY...

Write a C Program

Consider the following program fragment: 1. i = 1; sum=0; 2. while (i <= n) do begin 3. sum = sum + a; 4. i = i + 1; end...

Digital electronics problem

A logic circuit has three Boolean inputs X,Y and Z. Its output is F(X,Y,Z) such that: F(X,Y,Z) = 1 if aX + bY +cZ > d = 0 otherwise. a,b,c,d...

Write an efficient algorithm to find the first non-repeated character in a string defined

Write an efficient algorithm to find the first non-repeated character in a string defined over the English alphabet set [a-z, A-Z]. For example, the first non-repeated character in teeter is...