Received: from C9Mailgw01.prontomail.com ([216.163.188.10]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7I0mvP43712 for ; Fri, 17 Aug 2001 20:48:57 -0400 (EDT) (envelope-from rdg@viafractal.com.br) Received: by C9Mailgw01.prontomail.com (NPlex 5.5.042) id 3B7BD1800004B408 for pgsql-sql@postgresql.org; Fri, 17 Aug 2001 17:42:12 -0700 Received: from 200.201.141.157 by SmtpServer for ; Sat, 18 Aug 2001 00:42:10 +0000 Message-ID: <3B7DBE38.9F1FF640@viafractal.com.br> Date: Fri, 17 Aug 2001 22:00:40 -0300 From: Renato De Giovanni X-Mailer: Mozilla 4.72 [en] (Win95; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Nested set model Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200108/231 X-Sequence-Number: 4463 Hi, I'm trying the "nested set model" to handle a tree structure in a database (reference: http://www.dbmsmag.com/9603d06.html). It has many advantages if you want to select all nodes above or below a specific node (recursive calls aren't necessary), but when you want to select only the first generation under a node, the query I found was a lot more complex than it would be if using the traditional adjacency model. Considering this example: CREATE TABLE skill ( key INTEGER NOT NULL, name VARCHAR(50) NOT NULL, left_n INTEGER NOT NULL, right_n INTEGER NOT NULL, PRIMARY KEY (key), CHECK (left_n > 0 AND right_n > left_n) ); insert into skill values (1 , 'Skills' , 1, 30); insert into skill values (2 , 'Computing' , 2, 29); insert into skill values (3 , 'Programming', 3, 10); insert into skill values (4 , 'C++' , 4, 5); insert into skill values (5 , 'Java' , 6, 7); insert into skill values (6 , 'Prolog' , 8, 9); insert into skill values (7 , 'Database' , 11, 18); insert into skill values (8 , 'Oracle' , 12, 13); insert into skill values (9 , 'PostgreSQL' , 14, 15); insert into skill values (10, 'Solid' , 16, 17); insert into skill values (11, 'Design' , 19, 28); insert into skill values (12, 'CorelDraw' , 20, 21); insert into skill values (13, 'Illustrator', 22, 23); insert into skill values (14, 'Photoshop' , 24, 25); insert into skill values (15, 'The Gimp' , 26, 27); How could we select, for example, only the nodes immediately under "computing"? The only way I could do it was using: select son.key, son.name, son.left_n from skill son, skill parent where parent.key = 2 and son.left_n between parent.left_n and parent.right_n and son.key <> parent.key and son.key not in (select son_descendents.key from skill parent, skill son, skill son_descendents where parent.key = 2 and son.left_n between parent.left_n and parent.right_n and son.key <> parent.key and son_descendents.left_n between son.left_n and son.right_n and son.key <> son_descendents.key) order by son.left_n ; Isn't there an easier way to achieve this?? With the usual adjacency model the query would look trivial! Something like: select son.key, son.name from skill son where son.parent = 2 ; Well, thanks in advance! -- Renato Sao Paulo - SP - Brasil rdg@viafractal.com.br