Content-Type: text/plain;
  charset="us-ascii"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: pgsql-sql@postgresql.org
Subject: Isn't there a better way?
Date: Thu, 13 Jun 2002 12:41:10 -0700
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200206131241.10909.josh@agliodbs.com>

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) NOT NULL,
	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,
	constraint pk_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 NULL REFERENCES 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 t=
ree=20
barnch, leaving only the user references which are "lowest" on the tree.  T=
he=20
best I've been able to come up with is:

v_left :=3D current branch left node
v_right :=3D 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_users tu JOIN teams tm USING (team_id)
		WHERE ((tm.lnode > v_left and tm.rnode < v_right)
			OR (tm.lnode < v_left AND tm.rnode > v_right))
		GROUP BY user_id
		HAVING MIN(tm.lnode) < MAX(tm.lnode) AND
			tu.user_id =3D tu2.user_id
			AND MAX(tm.lnode) > teams.lnode)
	AND teams_users.team_id =3D tu2.team_id and teams_users.user_id =3D tu2.us=
er_id);

But that's a nested WHERE EXISTS clause, with an aggregate referenceing the=
=20
same aggregated view twice.   It seems like there must be a more efficient=
=20
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.