Received: from phc.net (smtp.phc.net [209.133.96.11]) by postgresql.org (8.11.6/8.11.4) with ESMTP id g1KLd0F31732 for ; Wed, 20 Feb 2002 16:39:00 -0500 (EST) (envelope-from emberson@phc.net) Received: from phc.net (209.133.25.173) by phc.net with ESMTP (Eudora Internet Mail Server 3.0.3) for ; Wed, 20 Feb 2002 13:39:00 -0800 Message-ID: <3C741A50.A9FA6212@phc.net> Date: Wed, 20 Feb 2002 13:51:12 -0800 From: Richard Emberson X-Mailer: Mozilla 4.78 [en] (X11; U; Linux 2.4.9-21 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Matching columns in rows from two tables Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200202/287 X-Sequence-Number: 6710 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