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
