Re: Reflexive table problem - tricky

From: james (nospam_at_hypercon.net)
Date: 10/14/04 

Date: Thu, 14 Oct 2004 10:50:18 -0500

Joe, the only problem here is that I provided my setup for you but I do not
have .lft and .rgt so I am not quite sure what you are telling me to do. If
you are telling me to re-design my entire table layout it isn't that easy as
we have applications accessing the data that I cannot mess with. When I
read your paper on the left right set way of modelling trees it looked as
though you had to design your table that way or am I not understanding it at
all. Bear with me here as I am not really an SQL guru, I am more of an
intermediate dabbler ;-)

JIM

"Joe Celko" <jcelko212@earthlink.net> wrote in message
news:OwOfYCZsEHA.2536@TK2MSFTNGP11.phx.gbl...
>>> have a standard Reflexive table and I need a way to take a given
> sub-tree within the table and flatten it out to a Table or View so that
> I can use that View or Table
> as Input to a Crystal Report. <<
>
> The term is adjacency list model. Can you explain "flatten it out" a
> bit more clearly. If you want to get the levels of tha tree, then that
> can be done in a VIEW with the nested sets model.
>
> The immediate superior is given by:
>
> SELECT B.emp AS boss, E.emp
> FROM OrgChart AS E
> LEFT OUTER JOIN
> OrgChart AS B
> ON B.lft
> = (SELECT MAX(lft)
> FROM OrgChart AS S
> WHERE E.lft > S.lft
> AND E.lft < S.rgt);
>
> or more generally, to find the level of each emp, so you can print the
> tree as an indented listing. Technically, you should declare a cursor
> to go with the ORDER BY clause.
>
> SELECT COUNT(O2.emp) AS indentation, O1.emp
> FROM OrgChart AS O1, OrgChart AS O2
> WHERE O1.lft BETWEEN O2.lft AND O2.rgt
> GROUP BY O1.lft, O1.emp
> ORDER BY O1.lft;
>
> Just add a parameter for the subtree root node and do some math.
>
> --CELKO--
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Sample data is also a good idea, along with clear
> specifications.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Generating binary and ternary trees in teh neste4d sets model
    ... There are many ways to represent a tree or hierarchy in SQL. ... define a simple OrgChart table like this. ... Is there a simple way to generate a nested sets model of a complete ...
    (comp.databases.theory)
  • Re: Complex SQL problem
    ... CREATE TABLE OrgChart ... SQL is a set oriented language, this is a better model than the usual ... lft and rgt columns is called the adjacency list model, ... To show a tree as nested sets, replace the emps with ovals, then nest ...
    (microsoft.public.access.formscoding)
  • Re: Help with ROW_NUMBER and recursive query
    ... There are many ways to represent a tree or hierarchy in SQL. ... Let us define a simple OrgChart table like ...
    (microsoft.public.sqlserver.programming)
  • self join
    ... The usual example of a tree structure in SQL books is ... CREATE TABLE OrgChart ... (emp CHAR(10) ... To show a tree as nested sets, ...
    (microsoft.public.access.gettingstarted)
  • Re: Comprable technique to Yukons "WITH", in 2000?
    ... There are many ways to represent a tree or hierarchy in SQL. ... called an adjacency list model and it looks like this: ... CREATE TABLE OrgChart ... To convert an adjacency list to a nested set model, ...
    (microsoft.public.sqlserver.programming)
Quantcast