Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12

    Question Unanswered: Selfjoin problem with tree structure

    I have the following table
    Memberid int
    submemberid int

    example
    MemberId SubMemberId

    1 2
    2 3
    1 4
    3 5
    2 6
    7 8

    Each member may supervise more than one member under him (whom I call submember)
    and then each sub member may also supervise more than one member under him
    I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so
    on
    I do not know how to do this here is my trial:



    ALTER Procedure dbo.MemberReports;1

    @MemberMaskId nvarchar(8)


    As
    SET NOCOUNT ON
    Begin
    Declare @Exists Int -- Return Value

    -- Select * from MembersTree where MemberId = @memId or MemberId = subMemberId


    SELECT
    MembersTree.memberid,
    MembersTree.submemberid,
    MembersTree1.memberid
    FROM
    MembersTree
    INNER JOIN
    MembersTree MembersTree1
    ON MembersTree.Submemberid
    = MembersTree1.memberid

    WHERE
    MembersTree.MemberId = @memId

    If @@rowcount = 0 -- No Record returned
    Select @Exists = 0
    Else
    Select @Exists = 1
    Return @Exists

    End

    Please help,
    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This needs some tweaking....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 char(1), Col2 char(1))
    GO
    
    INSERT INTO myTable99(Col1, Col2)
    SELECT 'A','B' UNION ALL
    SELECT 'A','C' UNION ALL
    SELECT 'A','D' UNION ALL
    SELECT 'C','E' UNION ALL
    SELECT 'C','F' UNION ALL
    SELECT 'C','G' UNION ALL
    SELECT 'G','H'
    GO
    
    DECLARE @x TABLE (Col1 varchar(8000))
    
    DECLARE @Col1 char(1), @Level int
    
    SELECT @LEVEL = 1
    
    INSERT INTO @x(Col1)
         SELECT Col1 + ' ' + Col2
           FROM myTable99
          WHERE Col1 = 'A'
    
    WHILE @@ROWCOUNT <> 0
      BEGIN
    
    SELECT @LEVEL = @LEVEL + 1
    
    INSERT INTO @x(Col1)
         SELECT SPACE(@LEVEL + 2) + a.Col1 + ' ' + a.Col2
           FROM myTable99 a
           JOIN @x b 
    	 ON a.Col1 = SUBSTRING(b.Col1,@LEVEL + 1,1)
      END
    	
    SELECT * FROM @x
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Joe Celko has a great solution for this kind of problem in his SQL For Smarties book, but it would require restructuring the data. A table-valued function could solve this problem either iteratively or recursively.

    -PatP

Posting Permissions

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