Re: Recursive join - blind alley?

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 3 Jan 2004 08:06:38 -0800
Message-ID: <a264e7ea.0401030806.496a2227_at_posting.google.com>

>> I've done a fair bit of research. Which frequently brings up the
words 'Joe Celko' and 'BOM'. <<

Now I am obligated to provide a nested sets solution! Let me be sloppy and not put on all of the constraints for a tree:

CREATE TABLE Events
(event_name VARCHAR(35) NOT NULL,
 performance_nbr INTEGER NOT NULL, -- or date?  performer_id INTEGER NOT NULL

          REFERENCES Performers (performer_id)
          ON UPDATE CASCADE,

 fee DECIMAL(14,4) NOT NULL CHECK (fee >= 0.00),  lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),  rgt INTEGER NOT NULL UNIQUE,
 CHECK (lft < rgt),
 PRIMARY KEY (event_name, performance_nbr, performer_id) );

When you put in a node, put it in at the appropriate level in the tree. An arranger shows up at the highest event level for all performances (do they get paid by performance or by show? I don't know this trade). A nose flute player shows up at the lowest levels like "Elvis-2004 Tour; jail house rock number" and performances 1,2,3 and 7.

Now the regular hierarchical summations will work. Received on Sat Jan 03 2004 - 17:06:38 CET