Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003

    Unanswered: Recursive Data Query(e.g Heading- sub heading)

    we have a table called evaluation_questions, the table has following fields
    queId,Question, level, parentId
    the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading,
    the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question.
    where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....

    queId	Question		level	parentId
    1	English			0	0
    2	Reading			1	1
    3	Writing			1	1
    4	recognizing words	1	2
    5	Fluency			2	2
    so English is a heading as level is 0 and has no parent as parentId is also 0.
    Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English
    same is the case with writing
    where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.


    Now What i want is to retrieve the all the questions and headings under a specified if i pass parentID as a parameter to stored
    procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.

  2. #2
    Join Date
    Feb 2004
    How far did you come with the procedure?

  3. #3
    Join Date
    Jan 2003

    i am far away

    need a hint to start doing something

  4. #4
    Join Date
    Jun 2003
    Provided Answers: 1
    If you know that you will have no more than three levels to your hierarchy, then you are probably better off coding Headings, Subheadings, and Questions as separate tables. The primary purpose of recursive table relationships is to handle cases where the number of levels is unknown and potentially unlimited.

    If it is the case that you can have additional levels in your tree, then within TSQL the use of an accumlator table is generally easier to program and more efficient to process than a true recursive query.

    An accumulator table is a temporary table that will hold your output dataset. You initially populate the table with a single seed record (the ID submitted as a paramter). You then create a loop that inserts into the accumulator table all the children of records that are already present in the table, unless the child record itself already exists in the table.

    When the rowcount returned by the insert statement is 0, you have gathered all the child records and you exit your loop.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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