Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recursive select? Expand / Collapse
Author
Message
Posted Tuesday, July 31, 2012 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
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?
Post #1337767
Posted Tuesday, July 31, 2012 4:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
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
Post #1337776
Posted Tuesday, July 31, 2012 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 31, 2014 7:15 PM
Points: 1,129, Visits: 1,163
http://www.sqlservercentral.com/Forums/Topic1337063-391-1.aspx


~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one
Post #1337790
Posted Tuesday, July 31, 2012 5:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
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
Post #1337791
Posted Tuesday, July 31, 2012 5:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
Thanks for the link ... I'm just seeing a blank page.
Post #1337799
Posted Tuesday, July 31, 2012 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062


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
Post #1337802
Posted Tuesday, July 31, 2012 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
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
Post #1337804
Posted Tuesday, July 31, 2012 6:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
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.
Post #1337825
Posted Tuesday, July 31, 2012 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
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
Post #1337837
Posted Tuesday, July 31, 2012 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:33 PM
Points: 191, Visits: 412
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.
Post #1337863
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse