SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recursive select?


Recursive select?

Author
Message
webskater
webskater
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 484

CREATE TABLE #tblItems
(
ItemID int,
ItemDescription varchar(50)
)
GO
INSERT INTO #tblItems
(ItemID, ItemDescription)
SELECT 1, 'Item 1' UNION ALL
SELECT 2, 'Item 2' UNION ALL
SELECT 3, 'Item 3' UNION ALL
SELECT 4, 'Item 4' UNION ALL
SELECT 5, 'Item 5' UNION ALL
SELECT 6, 'Item 6' UNION ALL
SELECT 7, 'Item 7' UNION ALL
SELECT 8, 'Item 8' UNION ALL
SELECT 9, 'Item 9' UNION ALL
SELECT 10, 'Item 10'
GO

CREATE TABLE #tblItemRelationship
(
ParentItemID int,
ChildItemID int
)
GO
INSERT INTO #tblItemRelationship
(ParentItemID, ChildItemID)
SELECT 1, 2 UNION ALL
SELECT 2, 3 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 2, 5 UNION ALL
SELECT 3, 6 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 8 UNION ALL
SELECT 7, 9 UNION ALL
SELECT 7, 10
GO




So, tblItems is a list of items and tblItemRelationship shows a parent child relationship between the items.

How do you write a select statement that would do this ... take an ItemID as a ParentID and show its children and show their children in a hierarchy.

So, for example, 'show me the children of Item ID 1' and you'd end up with data that would allow you to show data something like this.

Item 1
--Item 2
----Item 3
------Item 6
------Item 7
--------Item 9
--------Item 10
----Item 4
------Item 8
----Item 5

i,e. Item 1 has Item 2 below it.
Item 2 has Items 3,4 and 5 below it
Item 3 has Items 6 and 7 below it
Item 4 has Item 8 below it
etc.

I've been racking my feeble brain over this for days - my efforts involve endless temporary tables and inserts from one table to another and so on. Is there a simple way to do this?
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43089 Visits: 20015
Here's a rCTE to get you started:

;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
)
SELECT *
FROM rCTE



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
demonfox
demonfox
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2201 Visits: 1192
http://www.sqlservercentral.com/Forums/Topic1337063-391-1.aspx


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm
webskater
webskater
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 484
Hi and thanks very much for that.

It produces in the list column:

1>2
1>2>3
1>2>4
1>2>5
1>2>4>8
1>2>3>6
1>2>3>7>9
1>2>3>7>10

What I need it to produce is:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>9
1>2>3>7>10
1>2>4
1>2>4>8
1>2>5

I've tried various sorting but I can't make it do that. And although it's producing:
1>2
1>2>3
1>2>4
1>2>5

it is missing out:
1>2>3>7
webskater
webskater
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 484
Thanks for the link ... I'm just seeing a blank page.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43089 Visits: 20015


This link doesn't work.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43089 Visits: 20015
Here's the output of the rCTE:
Level ParentItemID ChildItemID List
1 1 2 1>2
2 2 3 1>2>3
2 2 4 1>2>4
2 2 5 1>2>5
3 4 8 1>2>4>8
3 3 6 1>2>3>6
3 3 7 1>2>3>7
4 7 9 1>2>3>7>9
4 7 10 1>2>3>7>10

sku370870 (7/31/2012)
Hi and thanks very much for that.

It produces in the list column:

1>2
1>2>3
1>2>4
1>2>5
1>2>4>8
1>2>3>6
1>2>3>7>9
1>2>3>7>10

What I need it to produce is:
1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>9
1>2>3>7>10
1>2>4
1>2>4>8
1>2>5

I've tried various sorting but I can't make it do that. And although it's producing:
1>2
1>2>3
1>2>4
1>2>5

it is missing out:
1>2>3>7


Looks ok to me. Try
ORDER BY List



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
webskater
webskater
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 484
Apologies - you're right - bit of brain freeze here I guess.

If I sort by List I get:

1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>10
1>2>3>7>9
1>2>4
1>2>4>8
1>2>5

So, the only niggle is that 1>2>3>7>10 is appearing before 1>2>3>7>9

I've tried this:


;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT TOP 100 PERCENT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
ORDER BY lr.ChildItemID ASC
)
SELECT *
FROM rCTE
ORDER BY List



bit it does not affect the sort order. Thanks again for your help.
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43089 Visits: 20015
sku370870 (7/31/2012)
Apologies - you're right - bit of brain freeze here I guess.

If I sort by List I get:

1>2
1>2>3
1>2>3>6
1>2>3>7
1>2>3>7>10
1>2>3>7>9
1>2>4
1>2>4>8
1>2>5

So, the only niggle is that 1>2>3>7>10 is appearing before 1>2>3>7>9

I've tried this:


;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT TOP 100 PERCENT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
ORDER BY lr.ChildItemID ASC
)
SELECT *
FROM rCTE
ORDER BY List



bit it does not affect the sort order. Thanks again for your help.


'1' appears before '9' - it's a string. Putting in a leading zero fixes this:

;WITH rCTE AS (
SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(
RIGHT('0'+CAST(tr.ParentItemID AS VARCHAR(10)),2)
+ '>' +
RIGHT('0'+CAST(tr.ChildItemID AS VARCHAR(10)),2)
AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1
UNION ALL
SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' +
RIGHT('0'+CAST(tr.ChildItemID AS VARCHAR(10)),2)
AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID
)
SELECT *
FROM rCTE



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
webskater
webskater
Right there with Babe
Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)Right there with Babe (754 reputation)

Group: General Forum Members
Points: 754 Visits: 484
I'm trying to understand your code ...

In this example your first select statement

SELECT
[Level] = 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(CAST(tr.ParentItemID AS VARCHAR(10)) + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM #tblItemRelationship tr
WHERE ParentItemID = 1



selects one row from #tblItemRelationship table which matches the parentID we're interested in and aliases this as 'tr'

Your second select ...

SELECT
[Level] = lr.[Level] + 1,
tr.ParentItemID, tr.ChildItemID,
List = CAST(lr.List + '>' + CAST(tr.ChildItemID AS VARCHAR(10)) AS VARCHAR(8000))
FROM rCTE lr
INNER JOIN #tblItemRelationship tr ON tr.ParentItemID = lr.ChildItemID



effectively joins #tblItemRelationship to itself - to the alias 'tr'

But 'tr' only contains one row - I don't understand how/why the derived(?) table rCTE manages to seem to do multiple joins to itself ... and the syntax - there is a 'with rCTE' statement that seems to enclose all the code, and then rCTE is aliased as 'lr' ... whole thing baffles me and I'd really like to understand. I wonder if you could explain please.

Thanks again.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search