From: Richard Emberson <emberson@phc.net>
To: pgsql-sql@postgresql.org
Subject: Matching columns in rows from two tables
Date: Wed, 20 Feb 2002 13:51:12 -0800
Message-ID: <3C741A50.A9FA6212@phc.net> (raw)
I have the following tables:
-- all of the users
-- user_id are unique
CREATE TABLE users (
user_id BIGINT,
....
PRIMARY KEY (user_id)
);
-- all of the groups
-- user_id are unique
CREATE TABLE groups (
group_id BIGINT,
...
PRIMARY KEY (group_id)
);
-- all of the group sets
-- group_set_id are unique
CREATE TABLE group_sets (
group_set_id BIGINT,
...
PRIMARY KEY (group_set_id)
);
-- a user can be a member of one or more groups
CREATE TABLE membership (
user_id BIGINT,
group_id BIGINT,
....
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (group_id) REFERENCES groups (group_id)
);
-- a group set is a set of one or more groups
CREATE TABLE group_sets (
group_id BIGINT,
group_set_id BIGINT,
....
FOREIGN KEY (group_id) REFERENCES groups (group_id),
FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
);
I want to:
Find the group_set_id such that for a given user_id (input
parameter)
there is a one-to-one correspondence between the group_ids
associated
with the user_id and the group_ids associated with the group_set_id;
for every group_id that the user_id has, the group_set_id also has
it
and for every group_id that the group_set_id has, the user_id also
has it.
If there is no such group_set_id, then return null.
What query will generate the group_set_id?
There ought to be some combination of joins, intersections, etc. that
can generate the result
but I am getting hungup on the fact that the number of group_ids being
matched is not fixed.
Thanks for any help.
Richard
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: emberson@phc.net
Subject: Re: Matching columns in rows from two tables
In-Reply-To: <3C741A50.A9FA6212@phc.net>
* 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