Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2001
    Location
    Southend-on-Sea/England
    Posts
    8

    Question Unanswered: Querying a Self Referencing Table

    Hi

    I have the following table:-

    childid parentid description
    ---------------------------------------------
    1 1 Company 1
    2 1 Office 1
    3 2 Ground Floor
    4 3 Section
    5 4 Room 1
    6 4 Room 2
    7 4 Room 3
    8 1 Office 2
    9 1 Office 3

    If I want the details of childid 6 I would like to return:-
    Company 1 - Office 1 - Room 2
    or
    childid 9 would return
    Company 1 - Office 3

    How do I write a sql statement to do this?

    Regards
    Tim

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    here is one solution...

    Code:
    --------------------------------------------------------------------------------------------------
    create table #tbl(childid int, parentid int, description varchar(15))
    insert into #tbl values(1, 1, 'Company 1')
    insert into #tbl values(2, 1, 'Office 1')
    insert into #tbl values(3, 2, 'Ground Floor')
    insert into #tbl values(4, 3, 'Section')
    insert into #tbl values(5, 4, 'Room 1')
    insert into #tbl values(6, 4, 'Room 2')
    insert into #tbl values(7, 4, 'Room 3')
    insert into #tbl values(8, 1, 'Office 2')
    insert into #tbl values(9, 1, 'Office 3')

    declare @key int, @child int, @parent int, @Answer varchar(100)
    select @key = 9
    , @Exit = 0
    select @Answer = description
    , @child = childid
    , @parent = parentid
    , @key = parentid
    from #tbl
    where childid = @key
    while (@child <> @parent) begin
    select @Answer = description + ' - ' + @Answer
    , @child = childid
    , @parent = parentid
    , @key = parentid
    from #tbl
    where childid = @key
    end
    select @Answer

    --------------------------------------------------------------------------------------------
    Last edited by Paul Young; 06-25-02 at 12:45.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Jul 2001
    Location
    Southend-on-Sea/England
    Posts
    8
    Paul, Thanks that worked.

    One more question - how would I find the maximum number of levels?
    a) for a given childid
    b) for the whole table (how deep does the deepest level go)

Posting Permissions

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