Results 1 to 4 of 4

Thread: self join.

  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: self join.

    Hi guys,
    I have a doubt. I have a table structure like


    id names parentid
    --------------------------
    1 name1 0
    2 name2 1
    3 name3 4
    4 name4 0
    5 name5 1
    6 name6 0


    based on parentid's value zero (0), we will see the names. hence first is name1. Now id for name1 is 1. Again we will check out parentid with value 1 (which we fetch from id column). Result is name2 and name 5.

    hence output is :-

    name1
    name2
    name5

    again if we proceed to next zero, value is name4 and id for name4 is "4". Hence for "4" in parentid we get name3 as result..

    final result will be :-

    name1
    name2
    name5
    name4
    name3
    name6

    thanks guys

    looking forwards for your assistance

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try
    Code:
    WITH CTE (AdamId, SeqNr, Id, Names, ParentId)
    AS
    (SELECT Id, 0, Id, Names, ParentId
    FROM #DaTable
    WHERE parentId = 0
    	UNION ALL
    SELECT CTE.AdamId, #DaTable.Id, #DaTable.Id, #DaTable.names, CTE.ParentId
    FROM #DaTable
    	INNER JOIN CTE ON
    		#DaTable.parentId = CTE.Id
    )
    SELECT * 
    FROM CTE
    ORDER BY AdamId, SeqNr
    Does the column "names" contain more than 1 name, like first name and last name? If not, change the column name to "name".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    153

    Smile it's not working

    Thanks a lot buddy but it's not working .
    My table's name is "testdemo". Now, could you write a query for me?


    Thank you

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by sunny_007 View Post
    My table's name is "testdemo". Now, could you write a query for me?
    Do a search-and-replace on my code: replace "#DaTable" by "testdemo"
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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