Am Freitag, 13. Mai 2005 10.32 schrieb Marcus Bointon: > I have a table that represents a tree structure via a self-join. I'd > like to get hold of all parent records in a single query - is such a > thing possible? e.g. given > > id parentid > 1 0 > 2 1 > 3 2 > 4 2 > 5 1 > 6 4
There is an alternative way of modelling tree structures in a relational db, "nested sets", it's more complex, but your requirements (and others) can be reached with a single statement. I'm just evaluating this thing, so I have no experience with it, but it sounds very good. Here is a link: http://www.intelligententerprise.com/001020/celko1_1.jhtml There are also perl modules (where you could get statements for specific task): http://search.cpan.org/~djcp/DBIx-Tree-NestedSet-0.16/lib/DBIx/Tree/NestedSet.pm === from the first link: CREATE TABLE Personnel (emp CHAR(10) NOT NULL PRIMARY KEY, lft INTEGER NOT NULL UNIQUE CHECK (lft > 0), rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1), CONSTRAINT order_okay CHECK (lft < rgt) ); 1. Find an employee and all his/her supervisors, no matter how deep the tree. SELECT P2.* FROM Personnel AS P1, Personnel AS P2 WHERE P1.lft BETWEEN P2.lft AND P2.rgt AND P1.emp = :myemployee; joe > If I was starting with record 4, I would want it to return records 2 > and 1 (probably in that order), starting from 5 would just give me 1 > etc. It needs to support arbitrary depth, hence the need for recursion. > > Can I do this in one go, or do I have to query iteratively until I > encounter a zero reference? > > Marcus > -- > Marcus Bointon > Synchromedia Limited: Putting you in the picture > [EMAIL PROTECTED] | http://www.synchromedia.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
