From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Isn't there a better way?
Date: Thu, 13 Jun 2002 12:41:10 -0700
Message-ID: <200206131241.10909.josh@agliodbs.com> (raw)
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 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_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 = 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Isn't there a better way?
In-Reply-To: <200206131241.10909.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox