Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013

    Unanswered: Need help in Recurssive Query

    i have a table called test :- create table test (id int , Docname varchar(200), parentkey int, ContentType varchar(200))
    insert into test values (1,'ParentFolder',-3,'Folder')
    insert into test values (2,'ChildFolder1',1,'Folder')
    insert into test values ( 3,'ChildFolder1',2,'Folder')
    insert into test values (4,'ParentFolder 2',-3,'Folder')
    insert into test values (5,'test Folder 1',4,'Folder')
    insert into test values (6,'test Folder 2',4,'Folder')

    in this table id is the primarykey, i have a column called parentkey in this table, in this table records having parent-child relation

    ex:- id Name PrentKey
    1 test -3
    2 test2 1

    -3 is the parentkey here, 1 vlaue in the parentkey is the child of id 1,

    here if i give child id key i want to get the all names of that child n parent fileds. example :- if i give '2' as input parament i want out put like below :

    ID Path
    1-2 test-test2

    for this i m trying below query :-

    WITH Emp_CTE ( id,ParentKey,ContentType,Path,Level)
    AS (

    SELECT id , ParentKey,ContentType,
    CONVERT(varchar(1000),DocName) , 0 Level
    FROM test S
    WHERE id = 2


    SELECT SS.ID , SS.ParentKey,SS.ContentType,
    CONVERT(varchar(1000), ISNULL(SS.DocName,'')+'|'+ISNULL(Path,'') )
    ,Level +1
    FROM test SS
    INNER JOIN Emp_CTE ecte ON ecte.ParentKey = SS.ID

    SELECT CASE WHEN Path IS NULL THEN '' ELSE '|' END + Path as folderpath,
    * FROM Emp_CTE WHERE ContentType = 'Folder' AND ParentKey = -3

    ablove query is giving output as below :-

    ID Path
    1 test|test2

    here i want to display all the IDs , but i am able to append all the docnames from table , how to achive id's like 1-2?

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    MySQL does not support recursive queries - are you sure you are using it?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts