Received: from echna.ne.jp (echna.ne.jp [210.175.52.4]) by postgresql.org (Postfix) with ESMTP id CACB94758EE for ; Sun, 24 Feb 2002 09:58:12 -0500 (EST) Received: from vip333 [218.47.116.84] by echna.ne.jp with ESMTP (SMTPD32-6.06) id AF8C4F770280; Sun, 24 Feb 2002 23:58:20 +0900 Date: Sun, 24 Feb 2002 23:59:33 +0900 From: Masaru Sugawara To: Richard Emberson Subject: Re: Matching columns in rows from two tables Cc: pgsql-sql@postgresql.org In-Reply-To: <3C77BD47.BD3C748F@phc.net> References: <20020223151139.DE3F.RK73@echna.ne.jp> <3C77BD47.BD3C748F@phc.net> Errors-To: Message-Id: <20020224235923.2DA2.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/346 X-Sequence-Number: 6768 On Sat, 23 Feb 2002 08:03:19 -0800 Richard Emberson wrote: > Thank you very much for your help. I managed to create a query with only two select > statements > and none of the selects are inner-selects: > > SELECT group_set_id, count(*) FROM group_sets , membership > WHERE membership.user_id = <> > AND membership.group_id = group_sets.group_id > GROUP BY group_set_id > INTERSECT > SELECT group_set_id, count(*) FROM group_sets > GROUP BY group_set_id; > > There may still be a better (better performance) query, Quite so. I would think it's probably one of the best way. Practically, I found my query including "where exists/not exists" is awfully slow if there are about 1000+ rows. > but, again, this is the > best I could > come up with. > > Thanks. > > Richard > > > 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 ( Regards, Masaru Sugawara