Correct.

That is not quite what I want.

I...

Hi, I've modified the code:

SELECT Distinct #tblTemplates.TemplateName

FROM #tblTemplates

INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID

INNER JOIN (SELECT TemplateID, BlockID

...

SELECT Distinct #tblTemplates.TemplateName FROM #tblTemplates

INNER JOIN #tblBlocks ON #tblTemplates.TemplateID = #tblBlocks.TemplateID

INNER JOIN (SELECT TemplateID, BlockID

...

CREATE TABLE #tblTemplates

(

TemplateID int,

TemplateName varchar(50)

...

Apologies, I was modifying my first post when I thought - that will just confuse the issue. Can we start again please?

CREATE TABLE #tblBlocks

...

Thanks for your reply ... it's giving me the opposite of what I want because I phrased my question badly.

I have modified my temporary table in my first post to...

Unfortunately, the 8 or so apps that use this database - which are all timeline driven - don't use a calendar table. In other apps I have a calendar table...

Thank you both very much for your help. I was slow replying to Mark to thank him as I was working on the most awful bit of code - using...

Thank you both for your help.

Thanks very much for that. Looks great to me.

I've been plodding away all afternoon and ended up with this (don't claim to have come up with it, had some help...

Thanks for the link - I'm beginning to comprehend.

One final thing. If the items table contained a date column, how could one force every set of child items to be sorted by date?

CREATE TABLE #tblItems

...

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...

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...