Loading ...
Sorry, an error occurred while loading the content.
 

Hierarchal data

Expand Messages
  • suresh kompella
    hi i have a problem with showing hierarchal view of the data in VB6 using SQL 2000. The Data will look like this. Personal_Id Name Age
    Message 1 of 2 , Mar 5, 2003
      hi
      i have a problem with showing hierarchal view of the
      data in VB6 using SQL 2000.
      The Data will look like this.
      Personal_Id Name Age Introducer_Id
      1 Ben 27 0
      2 tom 22 1
      3 Banks 44 0
      5 James 33 1
      6 Stewart 26 2
      7 Krishna 45 2
      8 Hari 23 7
      9 masood 22 8
      10 kris 19 9

      Note: Introducer_Id 0 is to tell he doesnt have a
      Introducer.
      Now i got to show the search.
      the search is for the introducer of a person then data
      should look some what like this.
      For example : if the person is searching for the
      introducers of Kris then the view should be

      Ben
      Tom
      Krishna
      Hari
      Masood
      Kris


      0r

      Kris
      Masood
      Hari
      Krishna
      Tom
      Ben

      but not
      Ben
      Tom ---------------------------- James
      Stewart --- Krishna
      Hari
      Masood
      Kris

      i am not interested in showing all the siblings of the
      parent.
      i wrote a test query like this
      declare @Int_Id as int
      DECLARE @RowsAdded int
      set @Int_Id ='8'
      CREATE TABLE #Tmp (name varchar(255), id int)

      INSERT #Tmp SELECT kana_name, personal_id FROM
      Personal_details WHERE personal_id=@int_Id

      SELECT @RowsAdded = @@RowCount
      WHILE (@RowsAdded > 0)
      BEGIN
      INSERT #Tmp
      SELECT personal_details.kana_name ,
      personal_details.introduce_id
      FROM #tmp INNER JOIN personal_details on
      #Tmp.id = personal_details.Personal_id
      WHERE personal_details.personal_id = #Tmp.id
      SELECT @RowsAdded = @@RowCount
      end
      select * from #tmp

      but this is going into a infinate loop. the intended
      out put of this query is
      Masood 09
      Hari 08
      Krishna 07
      Tom 02
      Ben 01



      Could any one help me pl.
      Thanks in advance.

      suresh


      =====
      ************************************************************
      Suresh RK Kompella

      __________________________________________________
      Do you Yahoo!?
      Yahoo! Tax Center - forms, calculators, tips, more
      http://taxes.yahoo.com/
    • Dave Cline
      You can probably simplify this a bit - this will render: Personal_id Name Introducer_Id ... 10 kris 9 9 masood 8 8
      Message 2 of 2 , Mar 6, 2003
        You can probably simplify this a bit - this will render:

        Personal_id Name Introducer_Id
        ----------- ---------- -------------
        10 kris 9
        9 masood 8
        8 Hari 7
        7 Krishna 2
        2 tom 1
        1 Ben 0

        Luck,
        Dave Cline
        www.bangeye.com


        /*
        create table Personal_details (
        Personal_Id int
        ,kana_name varchar(10)
        ,Age int
        ,Introducer_Id int
        )

        insert Personal_details values(1, 'Ben' ,27, 0)
        insert Personal_details values(2, 'tom' ,22, 1)
        insert Personal_details values(3, 'Banks' ,44, 0)
        insert Personal_details values(5, 'James' ,33, 1)
        insert Personal_details values(6, 'Stewart' ,26, 2)
        insert Personal_details values(7, 'Krishna' ,45, 2)
        insert Personal_details values(8, 'Hari' ,23, 7)
        insert Personal_details values(9, 'masood' ,22, 8)
        insert Personal_details values(10,'kris' ,19, 9)

        drop table Personal_details
        */

        DECLARE @Selected_Id as int SET @Selected_Id ='10'
        DECLARE @LastIntroducer_Id as int
        DECLARE @Continue bit Set @Continue = 1

        CREATE TABLE #Tmp (Personal_id int, Name varchar(255), Introducer_Id int)

        SET NOCOUNT ON
        INSERT #Tmp
        SELECT Personal_Id, kana_name, Introducer_Id
        FROM Personal_details
        WHERE Personal_Id = @Selected_Id

        SELECT @LastIntroducer_Id = Introducer_Id FROM #Tmp WHERE Personal_Id =
        @Selected_Id

        WHILE @Continue = 1 BEGIN
        SELECT @Selected_Id = @LastIntroducer_Id, @LastIntroducer_Id =
        A.Introducer_Id
        FROM Personal_Details A
        WHERE A.Personal_Id = @LastIntroducer_Id

        IF @@RowCount <> 0 BEGIN
        INSERT #Tmp
        SELECT @Selected_Id, A.kana_name, @LastIntroducer_Id
        FROM personal_details A
        WHERE A.Personal_Id = @Selected_Id

        IF @LastIntroducer_Id = 0 BEGIN
        SET @Continue = 0
        BREAK
        END
        END
        ELSE
        BEGIN
        SET @Continue = 0
        END
        END

        SET NOCOUNT OFF
        SELECT * FROM #tmp
        DROP TABLE #tmp
      Your message has been successfully submitted and would be delivered to recipients shortly.