Posted by Anon on 10/13/95 11:25

Since putting this online I have come across this code
Find all the children of AAA. In order to avoid running forever, stop
after four levels.

WITH parent (pkey, ckey, lvl) AS
(SELECT DISTINCT
pkey
,pkey
,0
FROM trouble
WHERE pkey = ’AAA’
UNION ALL
SELECT C.pkey
,C.ckey
,P.lvl + 1
FROM trouble C
,parent P
WHERE P.ckey = C.pkey
AND P.lvl + 1 < 4
)
SELECT *
FROM parent;

However I do not have a lvl attribute and so I fear I would be creating
an infinite loop.



Anon wrote:
> Im wondering if someone could help me with a recursive style sql command
>
> section = SectionID, parent
>
> I want to be able to delete a row where SectionID=X but also delete any
> section that matches SectionID.
>
> Kinda like the hierarchy of folders on your harddrive.
>
> I want to delete any subfolders of the main folder I am deleting.
>
> Can anyone give me some direction, my SQL isn't up to all that much.
>
> Ive got the following but its more pseudo code rather than something I
> expect to work.
>
>
> DELETE *
> FROM jss_sections
> WHERE Parent=SectionID AND (DELETE * FROM jss_sections WHERE
> SectionID.new=Parent)
>
> TIA
>
> Simon

[Back to original message]