# Thread: Recursive SQL help

1. Registered User
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. Registered User
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. Registered User
Join Date
Nov 2003
Posts
2,983
Provided Answers: 23
Originally Posted by dlam00
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Provided Answers: 54

5. Registered User
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 12:21.

6. Registered User
Join Date
Aug 2004
Location
Dallas, Texas
Posts
831
Originally Posted by dlam00
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 07:03.

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
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

8. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
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.

10. Registered User
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. Registered User
Join Date
Nov 2004
Posts
1,428
Provided Answers: 4
Thank you for your code.

Can you also provide us with some sample data and the expected results?

12. Registered User
Join Date
Sep 2011
Posts
75
Hello,

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

13. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Provided Answers: 1
jassi, the CTE solution was already proposed by Pat and seconded by myself.

#### Posting Permissions

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