Hi! I have a tree table:
CREATE TABLE tree ( CONSTRAINT fktree FOREIGN KEY (parent) REFERENCES tree (dept), dept int primary key, --department parent int ); insert into tree values(1,1); insert into tree values(2,1); insert into tree values(3,2); and a history table: CREATE TABLE history ( CONSTRAINT fkhistory FOREIGN KEY (dept) REFERENCES tree (dept), dept int primary key, --department amount int ); insert into history values(1,100); insert into history values(2,200); insert into history values(3,300); My purpose is to retrieve the amount detail of department "1" and all departments under it. I have come out with 2 approaches: APPROACH A: --Returns TRUE if department $2 reports to department $1. CREATE FUNCTION IsChild(TEXT,TEXT) RETURNS BOOLEAN AS ' DECLARE p ALIAS FOR $1; --parent c ALIAS FOR $2; --child vparent INT; BEGIN IF c = p THEN RETURN TRUE; END IF; SELECT parent INTO vparent FROM tree WHERE dept=c; IF NOT FOUND THEN RETURN FALSE; ELSE RETURN IsChild(p,vparent); END IF; END' LANGUAGE 'plpgsql' STABLE; SELECT amount FROM history WHERE IsChild(1,dept); --------------------- APPROACH B: (Assuming the number of layers of this tree is predicatable. Let's take 3 layers as an example.) SELECT amount FROM history WHERE dept=1 OR dept IN (SELECT dept FROM tree WHERE parent=1) OR dept IN (SELECT dept FROM tree WHERE parent IN (SELECT dept FROM tree WHERE parent=1)); Both queries return amount -------- 100 200 300 (3 rows) APPROACH A is obviously easier to implement. My question is which approach gets better performance when the number of rows in history and the layers in tree grows? I don't intend to apply "Joy's worm" tree algorism as it is too complicate to me to understand. Thank you in advance for any input! Regards, CN -- http://www.fastmail.fm - A no graphics, no pop-ups email service ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]