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
|
Список | 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 по дате отправления: