Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (Postfix) with ESMTP id EBB73475873 for ; Sat, 23 Feb 2002 01:16:49 -0500 (EST) Received: from vip333 [210.175.55.204] by echna.ne.jp with ESMTP (SMTPD32-6.06) id A3A432880274; Sat, 23 Feb 2002 15:16:04 +0900 Date: Sat, 23 Feb 2002 15:18:09 +0900 From: Masaru Sugawara To: Richard Emberson Subject: Re: Matching columns in rows from two tables Cc: pgsql-sql@postgresql.org In-Reply-To: <3C741A50.A9FA6212@phc.net> References: <3C741A50.A9FA6212@phc.net> Errors-To: Message-Id: <20020223151139.DE3F.RK73@echna.ne.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.08 X-Archive-Number: 200202/334 X-Sequence-Number: 6756 On Wed, 20 Feb 2002 13:51:12 -0800 Richard Emberson wrote: > I have the following tables: > ... > -- a group set is a set of one or more groups > CREATE TABLE group_sets ( Group_sets is already defined, isn't it ? > 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? I'm not sure whether or not I have understood what you described. If I use organizations instead of the 2nd group_sets you were created, presumably ... -- using no joins select org1.group_set_id from organizations as org1 where exists (select org0.group_id from organizations as org0 where exists (select mem.group_id from membership as mem where org0.group_id = mem.group_id and user_id = <> group by mem.group_id having count(*) = 1 ) and org1.group_id = org0.group_id group by org0.group_id having count(*) = 1 ) ; -- using inner joins select org2.group_set_id from (select org0.group_id from (select group_id from membership where user_id = <> group by group_id having count(*) = 1 ) as mem inner join organizations as org0 on (mem.group_id = org0.group_id) group by org0.group_id having count(*) = 1 ) as org1 inner join organizations as org2 on (org1.group_id = org2.group_id) ; Regards, Masaru Sugawara