Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Unanswered: recursive queries

    I have a following structure

    table A
    userid ReferedBy
    B A
    C B

    Table B
    Userid compID
    A Alpha
    B self
    C self


    now the scenario is :
    the user A is from the company "Alpha"
    he introduces user B, who registers in the system his company bcomes "self",
    now B inturn refers user C who also registers in the system and his company is now again "self".
    Now I need to generate a report of number of users that have registered under one company, for eg.
    for the company "Alpha" no of users becomes 2 since A refered to two users and both of them have registered.

    I m stuck with the query.
    thanks in advance...
    regards,
    Harshal

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, despite the fact that is looks like you are working for a multi-level-marketing company, I'll give you a tip. Recursive querys are not the most efficient method of getting the results you want. It is better to create a table variable for accumulating child records.

    This algorithm works well:

    Create AccumulatorTable (RecordID)
    Add parent record to AccumulatorTable
    while @@Rowcount > 0
    Add children of all records in the AccumulatorTable to the AccumulatorTable, where the children are not already present in the AccumulatorTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why don't you add a third column, Originator, to your table B? And in this case A will have NULL, and B & C will both have A in this field. I know it's a step towards denormalization, but not unusual to make. And you'll avoid performance issues by running those massive loops (if the volume of data becomes tangible enough).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by rdjabarov
    Why don't you add a third column, Originator, to your table B? And in this case A will have NULL, and B & C will both have A in this field. I know it's a step towards denormalization, but not unusual to make. And you'll avoid performance issues by running those massive loops (if the volume of data becomes tangible enough).
    I have already done that added a parentcompany field in the table but i need to update the existing records ...

    and wrong guess blindman

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, so what is the database for?

    Adding the Originator column will solve the issue neatly if you are only dealing with one level, which is not a case of recursion anyway. If you want to be able to say "A has two decendents (B and C) and B has one decendent (C) etc..." getting a list of all descendants for each Record, then use the accumulator method. Can you see why I thought it might be MLM? How do we compensate Joe for all the cheap toothpaste purchased by Pete...?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Adding the Originator column will solve the issue neatly if you are only dealing with one level...
    That's EXACTLY what he is after, not a full retrieval of all parent-child relationships, in which case, as you said, a recursion algorythm would be very appropriate. Then there won't be any need for Originator column, because the structure already meets the requirements for recursion.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    sorry for being so late and not being elaborate on the post..
    it was a deadline so was not able to post.
    Quote Originally Posted by blindman
    OK, so what is the database for?
    its a database for a service provider to major mobile service carriers
    Adding the Originator column will solve the issue neatly if you are only dealing with one level, which is not a case of recursion anyway.
    ??
    If you want to be able to say "A has two decendents (B and C) and B has one decendent (C) etc..." getting a list of all descendants for each Record, then use the accumulator method. Can you see why I thought it might be MLM? How do we compensate Joe for all the cheap toothpaste purchased by Pete...?
    you r right.
    and this is what i wanted to do.

    now what I'm planning to do is add a column "parent company" in the table A,
    and update all the records for all the users who have been refered by the users company alpha, so I get the first level
    so A is from company Alpha
    B is refered by A so the parent company for B would become "Alpha"
    and further the table A should be updated to set the parent company of the refered user

    something like
    update tableA set parentcompany= b.parentcompany from tableA A,TableB b
    where a.referedby=b.userid


    do u guys think this will work???

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can a child record have only one parent? If so, then you really need only one table, holding the RecordID and ParentRecordID. From that you can derive the entire tree using either a recursive procedure or an accumulator table:

    Declare @Accumulataor table (RecordID as YourType)

    insert into @Accumulator (RecordID) values (YourParentRecord)

    while @@Rowcount > 0
    insert into @Accumulator (RecordID)
    select YourTableRecordID
    from YourTable
    inner join @Accumulator Accumulator on YourTable.ParentID = Accumulator.RecordID
    left outer join @Accumulator CurrentRecords on YourTable.RecordID = CurrentRecords.RecordID
    where CurrentRecords.RecordID is null

    Is that EXACTLY what you are after?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Dec 2002
    Location
    Raleigh, NC, USA
    Posts
    19

    RE: recursive queries question

    I am trying to manage an virtual File/Folder Structure. I am using the code you have provided (with some modifications; see below). I am trying to get the columns to show the folder by folder hierarchy, but have had no success. The code below works to show the current folder and its immediate parent, what do I need to do to show the grandparent folder, and/or great-granparent folder? Help would be most appreciated!

    I would like the output to look similar to this:

    RecordID | GrandParentName | ParentName | DocName |
    ------------------------------------------------------
    1________NULL_____________NULL________Root
    2________NULL_____________Root_________FolderA
    3________NULL_____________Root_________FolderB
    4________Root_____________FolderA_______Doc1
    5________Root_____________FolderB_______Doc2
    6________Root_____________FolderB_______FolderC
    7________FolderB___________FolderC_______Doc3

    -----------------------------------------------------


    '-----------------------------------------
    Current Table Structure
    '-----------------------------------------
    tblMenu
    idxMenu_PK ID
    Document_FK int
    ElementParent int


    tblDocuments
    idxDocuments_PK
    DocName char(50)
    '-----------------------------------------


    SQL Query
    '-----------------------------------------

    Declare @Accumulator table (RecordID int, ParentName char(50), DocName char(50))

    insert into @Accumulator (RecordID, ParentName, DocName) values (1, NULL, NULL)

    while @@Rowcount > 0
    insert into @Accumulator (RecordID, ParentName, DocName)
    select menu.Document_FK, doc.DocName, docname.DocName
    from tblMenu as menu
    inner join @Accumulator Accumulator on menu.ElementParent = Accumulator.RecordID
    inner join tblDocuments AS doc ON doc.idxDocuments_PK = Accumulator.RecordID
    left outer join @Accumulator CurrentRecords on menu.Document_FK = CurrentRecords.RecordID
    left outer join tblDocuments AS docname ON docname.idxDocuments_PK = menu.Document_FK
    where CurrentRecords.RecordID is null
    ORDER BY doc.DocName, docname.DocName

    SELECT * FROM @Accumulator
    Last edited by shaun27612; 07-19-04 at 17:19.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is awkward to do, because as soon as you accomodate GreatGrandParent, you'll be looking for GreatGreatGrandParent, and so on. The problem is that you are trying to return an open-ended number of columns. To deal with N columns, you'd have to write a stored procedure that dynamically modified a temporary table, adding columns for each iteration. This would be difficult to do, and once you finished it you'd find that your interface would choke on it becuase it would never know what record layout to expect.
    When I've done this in that past, I've returned a tree layout instead, like this:

    GreatGrandParent1
    ---GrandParent1
    ------Parent1
    ---------Child1
    ---------Child2
    ---------Child3
    ------Parent2
    ---------Child1
    ---------Child2
    ---------Child3
    ---GrandParent2
    ------Parent1
    ---------Child1
    ---------Child2
    ---------Child3
    ------Parent2
    ---------Child1
    ---------Child2
    ---------Child3

    ...etc...

    What are you planning to do with output?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Dec 2002
    Location
    Raleigh, NC, USA
    Posts
    19

    recursive queries

    I only need to go as far as the grand parent.

    The folks that wrote the business requirements ask for the list of documents and maps to be broken down by region then state and then by type in the tree structure. So if a recordset could be returned showing DocName, Parent, GrandParent then I'll be in business as this would reflect in the following way.

    Recordset
    -----------------
    DocName = Map1.pdf
    Parent = Topo
    GrandParent = Arkansas

    Interface
    -----------------
    Arkansas\Topo\Map1.pdf

    This would allow me to show what state the topo map belongs to. Keep in mind that the requirements are not changeable by me, as I understand that there may be better ways to organize the tree.

    Thanks in advance for all of your help.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Heck, you can do this easily:

    Select a.Record as Grandparent, b.Record as Parent, c.Record as Child
    from Yourtable a
    left outer join Yourtable b on a.recordID = b.parentID
    left outer join Yourtable c on b.recordID = c.parentID

    ..BUT......
    ...Region, State, and Type are arguably different "objects", and shouldn't be store in a recursived structure anyway. I mean, a State can't be a State of another State! Perhaps a region could be a subregion of another region, though you don't appear to be using that option. And I don't have a clue how "Type" fits in. Is it at least a geographic type?

    I think you should seriously consider storing these data in three different hierarchical tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2002
    Location
    Raleigh, NC, USA
    Posts
    19

    recursive question

    I apologize for not giving all the information. There are many things going on with this table and its data that I haven't included (due to time and keeping sanity together). The bottom line here is that I need it in recursive because (as strange as it may be) some states can be in different (multi) regions, due to the fact that some regions could overlap (i.e. SouthEast\Georgia and EastCoast\Georgia). Within these folders are different maps.

    So going back to my initial post, can you please help me add the Grandparent folder in a recursive query. That is what I need as I cannot rearrange the table/dbase structure for various other reasons.

    Again, thank you for all of your help.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That is still no reason for a recursive design, but whatever...

    What is wrong with the pseudo-code I posted?:

    Select a.Record as Grandparent, b.Record as Parent, c.Record as Child
    from Yourtable a
    left outer join Yourtable b on a.recordID = b.parentID
    left outer join Yourtable c on b.recordID = c.parentID

    That should work fine for a fixed number of hierarchical levels.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, you have remarkable patience

    just ask them to google adjacency model and remind them that true recursion should be done in memory, in arrays or some darned thing, and not by recursively calling the database

    there's a thread about this on, ahem, another database forum, where the guy ran a stress test on a few million rows, taking the left joins to 15 or so levels

    worked like a charm (url offline if desired)

    in the context of most implementations i've ever seen in threads like these, the most you want to dive down is 4 or 5 levels

    left join, left join, left join... why is everyone so afraid of a few left joins?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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