Results 1 to 13 of 13
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: Recursive SQL help

    Hi All,

    I have a a parts table with two fields of importance: Child Part Number and Parent Part Number. The Parent Part Number can also exists as a Child Part Number. Take a look below:

    Code:
    ChildPartNum       ParPartNum
    
    301301               445566
    301301               112233
    445566               998877
    998877               a22334
    This staggered hierarchy can happen for over 15 different levels. The requirement is this: I want to be able to select a part number (e.g. 301301) and see EVERY subsequent PARENT (parent, grand parent, great grand parent, etc) for the immediate (first level) parent. So 445566 is a parent to 301301. 998877 is parent to 445566...and so on.

    I can't even fathom how to start. I can get the two level using a union, but what do I do after that?

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Try looking at a self join. I have some old code for doing this somewhere. Hmmm...but I'm off to pedal home for a pint. Cheers

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by dlam00 View Post
    I can't even fathom how to start. I can get the two level using a union, but what do I do after that?
    If you are on SQL Server 2005 or later you can use a recursive common table expression (CTE) to query this kind of hierarchy.

    Should be something like this (if I understood your table structure correctly)

    Code:
    with tree as (
       SELECT childpartnum, parPartNum
       FROM the_table
       WHERE childpartnum = '301301'
       
       UNION ALL
       
       SELECT t1.childpartnum, t1.parPartNum
       FROM the_table t1
          JOIN tree p ON p.parPartNum = t1.childPartNum
    ) 
    SELECT *
    FROM tree
    If you need more details, search for recursive CTE in the web (or the SQL Server manual), there are loads of examples.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    Thanks for the fast response so far! One thing that I haven't gotten recursive to do (and it's because I didn't make myself clear in the first post) is that I need to return the parent value in the child column as well. (In the code below, the child column is "MtlPartNum" and the parent column is "PartNum".

    I tried shammat's example but it only returns part 304304. What I need is 304304 (the child), 400675(the parent), GMT10 (peer parent), 408-776-95 (grandparent) to all be listed in the MtlPartNum (child) column.

    Code:
    WITH PartUsed as (
    SELECT Company, MtlPartNum, RevisionNum, PartNum
    FROM PartMtl
    WHERE MtlPartNum = '304304'
    
    UNION ALL
    
    SELECT P1.Company, P1.MtlPartNum, P1.RevisionNum, P1.PartNum
    FROM PartMtl as P1
    JOIN PartUsed PU on PU.PartNum = P1.MtlPartNum
    )
    
    SELECT *
    FROM  PartUsed
    Returns:
    Code:
    Company	MtlPartNum	  RevisionNum	PartNum
    TEL	        304304	         A1	        400675
    TEL       	304304	         A1	        400675
    TEL	        304304	         A5	        GMT10
    TEL	        304304	         A5	        GMT10
    TEL	        304304	         A6	        GMT10
    I'm on SQL 2008 R2 and I'm building this in SQL Report Builder...although I'm dev'ing it in Server Mgmt Studio.
    Last edited by dlam00; 09-20-11 at 13:21.

  6. #6
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by dlam00 View Post
    What I need is 304304 (the child), 400675(the parent), GMT10 (peer parent), 408-776-95 (grandparent) to all be listed in the MtlPartNum (child) column.
    google "hierarchical recursive data sql 2005"
    Last edited by corncrowe; 09-21-11 at 08:03.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Pat's suggestion to use Common Table Expressions is the way to go.
    Follow his link, which should provide some examples.
    I also provide an example of tree searches in a presentation I gave, which is shared as a google doc: "CTEs the Easy Way": https://docs.google.com/present/edit...RqdzU&hl=en_US
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Sep 2011
    Posts
    4
    Thanks for all the responses. I've built the CTE query and implemented some other logic. The problem is the child partnum column is still only returning the part number in the where clause.

    This is the logic I am trying to do:

    1) Find all parents, grand parents, etc. for given part number
    2) Take the parent, grand parent, etc. part number and find their parent, grand parent, etc
    3) UNION all of the queries ran in step 1 and step 2 above so that they display in the same table.

    Basically, you'd run the the select statement with where clause for child part, run it again with parent part, again with grand parent, etc etc. Then you'd return all results in the table.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Why don't you show us the code you got, so we have something to talk about? It will be easier for us to spot the error.
    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

  10. #10
    Join Date
    Sep 2011
    Posts
    4
    Code:
    ;WITH MTL 
    AS 
    (
    SELECT Company, MtlPartNum , PartNum , QtyPer, RevisionNum, ParentAltMethod, ParentMtlSeq
    FROM PartMTL
    WHERE MtlPartNum = @PartNum
    UNION ALL
    SELECT A.Company, MTL.PartNum ChildPartNum, A.PartNum, A.QtyPer, A.RevisionNum, A.ParentAltMethod, A.ParentMtlSeq
    FROM MTL
    JOIN PartMTL A ON A.MtlPartNum = MTL.PartNum and A.MtlPartNum <> A.PartNum
    WHERE MTL.PartNum is not NULL
    )
    SELECT Distinct *
    FROM MTL
    WHERE PartNum is not NULL
    Order By MtlPartNum
    @PartNum is a parameter. MtlPartNum is the Child part num. Part Num is the parent part num.

    This actually gets me pretty darn close to my intended results. I'm returning too many rows so I need to find out what other where clauses I need to add.

  11. #11
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Thank you for your code.

    Can you also provide us with some sample data and the expected results?
    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

  12. #12
    Join Date
    Sep 2011
    Posts
    75
    Hello,

    You can use CTE for getting the hierarchical data having parent child relation ship

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    jassi, the CTE solution was already proposed by Pat and seconded by myself.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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