SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Tree structure

  1. #1
    SitePoint Enthusiast Roj2002's Avatar
    Join Date
    Jan 2002
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Tree structure

    I have a tree structure stored in a postgre database. It's a simple id/paren_id reference:
    Code:
    1/0
    |---2/1
    |---3/1
          |_4/3
          |   |_5/4
          |
          |_6/3
    where id/paren_id

    For I given ID I need to find my way back to the root. Right now I make this with a simple SELECT query:

    Select parent_id where id = $id

    $id = parent_id

    then do the Select again, and so on until I don't get anything back.

    This works fine, problem is, that I have to make quite a lot SQL queries. So I was wondering if there is a way, to get the "way" back to the root with only one query. I have only a very basic SQL knowledge, so I assume there is a better way to do this...

    Roj

  2. #2
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What do you want to accomplisch with that?

  3. #3
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,625
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I dont know PL/SQL but I believe you can make a recursive stored proc that will return the root id.

    PSUEDOCODE a la SQL Server:
    Code:
    Create proc GetRootParentID 
    @ID int
    as
    DECLARE @RetID int
    
    SELECT @RetID=(SELECT ParentID from vwCompanies WHERE ID=@ID)
    IF (@RetID=-1)
    begin
    	Return @RetID
    	Print @RetID
    end
    ELSE
    	Exec GetRootParentID @RetID
    WWB

  4. #4
    SitePoint Addict Avido's Avatar
    Join Date
    Jul 2003
    Location
    Kortrijk, Belgium, Europe, the world
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was wandering, will there be more than 1 parent or not, otherwise it is quite simple, isn't it


Bookmarks

Posting Permissions

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