Folks,
Given the following tables:
--DROP TABLE teams_desc;
create table teams_desc (teams_id INT4 NOT NULL DEFAULT NEXTVAL('users_user_id_sq') PRIMARY KEY,teams_name VARCHAR(75)
NOTNULL,teams_code VARCHAR(20) NOT NULL,notes TEXT NULL );
--drop table teams_tree;
create table teams_tree (teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,treeno INT4 NOT
NULL,constraintpk_teams_tree PRIMARY KEY (teams_id, treeno));
--drop table teams_users;
create table teams_users (teams_id INT4 NOT NULL REFERENCES teams_desc(teams_id) ON DELETE CASCADE,user_id INT4 NOT
NULLREFERENCES users(user_id) ON DELETE CASCADE,leader BOOLEAN NOT NULL DEFAULT FALSE,constraint teams_users_pk PRIMARY
KEY( teams_id, user_id )
);
drop view teams;
create view teams as
select teams_id, teams_name, teams_code, notes,min(treeno) as lnode, max(treeno) as rnode
from teams_desc JOIN teams_tree USING (teams_id)
group by teams_id, teams_name, teams_code, notes;
I need to construct a query that will delete all duplicate users within a tree
barnch, leaving only the user references which are "lowest" on the tree. The
best I've been able to come up with is:
v_left := current branch left node
v_right := current branch right node
DELETE FROM teams_users
WHERE EXISTS (SELECT teams.team_id
FROM teams JOIN teams_users tu2 USING (team_id)WHERE EXISTS (SELECT MAX(tm.lnode), MIN(tm.lnode), user_id FROM
teams_userstu JOIN teams tm USING (team_id) WHERE ((tm.lnode > v_left and tm.rnode < v_right) OR (tm.lnode <
v_leftAND tm.rnode > v_right)) GROUP BY user_id HAVING MIN(tm.lnode) < MAX(tm.lnode) AND tu.user_id =
tu2.user_id AND MAX(tm.lnode) > teams.lnode)AND teams_users.team_id = tu2.team_id and teams_users.user_id =
tu2.user_id);
But that's a nested WHERE EXISTS clause, with an aggregate referenceing the
same aggregated view twice. It seems like there must be a more efficient
way to build this query, but I can't think of one. Suggestions?
-Josh Berkus
P.S. This is based on Joe Celko's Linear Nested Model of tree construction.