Post reply

¿¿Recursive query??

  • December 3, 2003 at 11:44 am

    #84903

    Hello Gurus

    I have a task that needs to return node type information from a table.

    The table examples are as follows

    Table 1: Properties

    PID, Parent, Desc

    11p1

    21p2

    31p3

    42p4

    54p5

    …

    Pid is the property id

    Parent is the property id under which the property is grouped. All values in the parent column exist in the PID column.

    Desc – self explanatory

    The task that I am trying to perform is to extract a record from the property table and all its associated children, grandchildren…

    So for instance if I desired to get p1 and all associated children, I would return the whole table since p1 is the parent to the entire table. If I wanted p3 it would only return p3 since it does not have any children. If I wanted p2, the return set would contain p2, its child p4 and the child of p4 which is p5.

    I know that this can be done with recursion and am willing to accept that solution if it is the best way to go with this type of query, but I wanted to get your opinion on the best methodology for performing tasks of this type. I would rather not retrieve the information in xml format at this time, even though this has xml written all over it. My thought is that, once I have the correct query/algorithm, converting to an xml format will be a simple task.

    Thanks in advance

    Steve

    Edited by - kepr00 on 12/03/2003 11:44:34 AM

    Steve

  • December 3, 2003 at 11:58 am

    #484505

    recursion is limited to 32 levels if you need more than that do a quick search for handling hierarchies on sql mag and it should give you some pointers !!


    * Noel

  • December 4, 2003 at 6:51 am

    #484587

    That all depends on what you're doing with the Data...in other words, how do you plan to display this data?

    If it's in a User interface, your recursion code should go there.

    If it's on a Report...Good luck! "May the Force be with you!"

    I have a similar Table in my DB in which I also have a ParentID. Since it was for user display purposes, I simply did this with a TreeView. Reporting is a whole different story as there is absolutely no way to determine how deep the Recursion goes.

  • December 4, 2003 at 7:23 am

    #484602

    maybe I was not clear YOU DON'T HAVE TO USE RECURSION.

    I am including Three examples of what I mean.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915

    http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm

    http://www.sqlmag.com/Articles/Index.cfm?ArticleID=16123

    and those are NOT all the choices

    HTH


    * Noel

  • December 4, 2003 at 7:43 am

    #484609

    Thanks

    Noel

    Since I am using both Sql 7 and Sql 2k I will have to try out the Microsoft solution that you listed to see if there are any performance benefits.

    Steve

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply