Re: Nested sort, trying again

On Thu, 29 Sep 2005 16:08:10 -0400, Bob Stearns wrote:

(snip)
>I thought everyone on here would be familiar with Joe Celko's Nested Set
>representation of a tree;

Hi Bob,

I think you're right. But since your problem included just a bunch of column names without explanation or sample data to help us understand what each column was used for, it was not obvious that this was a hierarchy in the nested sets model.

(snip explanation of nested sets model)
>In this case the tree is sorted in the order I want to present it to my
>users. Consider the tree z(y(x,w),v(y(t,s))) which would have a similar
>representation to the first on but which I wish to present to my users as:
>
>z
> v
> u
> s
> t
> y
> w
> x
>The question is: Is there an easy (relatively) way to produce this sort
>without a recursive sql user defined function?

I don't have Joe's book here, but I believe that the *direct* children of a given node can be found with the following query:

SELECT     child.LOC_ID, child.NAMEX, child.ENTITY_ID, ...
FROM       IS3.LOCATIONS AS parent
INNER JOIN IS3.LOCATIONS AS child
      ON   child.LEFTX > parent.LEFTX
      AND  child.LEFTX < parent.RIGHTX
      AND NOT EXISTS (SELECT *
                      FROM   IS3.LOCATIONS AS btween
                      WHERE  btween.LEFTX > parent.LEFTX
                      AND    btween.LEFTX < child.LEFTX
                      AND    btween.RIGHTX > child.RIGHTX)
WHERE      parent.LOC_ID = _at_LOC_ID

(Note 1: I use the MS SQL Server convention to start variable names with an _at_ sign)
(Note 2: I didn't test this query. See www.aspfaq.com/5006 to see what data you need to supply if you prefer a tested solution - and note that I can only test against MS SQL Server 2000)

Oh, and to get this sorted in ascending name order, simply add

ORDER BY child.NAMEX

at the end of the query...

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)