Matching columns in rows from two tables

Поиск
Список
Период
Сортировка
От Richard Emberson
Тема Matching columns in rows from two tables
Дата
Msg-id 3C741A50.A9FA6212@phc.net
обсуждение исходный текст
Ответы Re: Matching columns in rows from two tables  (Masaru Sugawara <rk73@echna.ne.jp>)
Список 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)
REFERENCESusers (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)
REFERENCESgroups (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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Henry Holland
Дата:
Сообщение: Changing a column constraint?
Следующее
От: norvelle@ag.arizona.edu (Erik Norvelle)
Дата:
Сообщение: Trying to convert a TIMESTAMP return value to TIME