From: | Richard Emberson <emberson(at)phc(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Matching columns in rows from two tables |
Date: | 2002-02-20 21:51:12 |
Message-ID: | 3C741A50.A9FA6212@phc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-02-20 23:21:41 | Re: Dates and year 2000 |
Previous Message | Andy Marden | 2002-02-20 20:37:12 | Re: Dates and year 2000 |