X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 0CC5DD1B9AF
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Mar 2004 13:39:09 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 22310-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue,  9 Mar 2004 09:39:09 -0400 (AST)
Received: from mail.greatgulfhomes.com (unknown [216.94.125.243])
	by svr1.postgresql.org (Postfix) with ESMTP id 4E791D1B905
	for <pgsql-sql@postgresql.org>; Tue,  9 Mar 2004 09:39:06 -0400 (AST)
Received: from terrynew2 (firewall.development.greatgulfhomes.com
	[216.191.203.106]) by mail.greatgulfhomes.com (Postfix) with SMTP
	id 82EA436690F; Tue,  9 Mar 2004 08:43:00 -0500 (EST)
From: <terry@ashtonwoodshomes.com>
To: "'Matt Chatterley'" <zen31329@zen.co.uk>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Trying to make efficient "all vendors who can provide all items"
Date: Tue, 9 Mar 2004 08:37:48 -0500
Message-ID: <042301c405db$b63c5340$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
In-Reply-To: <Zen-1B0RXT-0003jc-3j@heisenberg.zen.co.uk>
Importance: Normal
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/79
X-Sequence-Number: 17178

Of all the proposed solutions, this appears to run the fastest, and not
require the creation of an additional table.

Thanks!

Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Matt Chatterley
> Sent: Monday, March 08, 2004 3:41 PM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Trying to make efficient "all vendors who
> can provide
> all items"
>
>
> Hmm. My PGSQL knowledge is rusty, so this may be slightly
> microsoftified..
>
> How about just:
>
> SELECT V.VendorID, V.VendorName, COUNT(IV.ItemID)
> FROM Vendor V
> INNER JOIN Item_Vendor IV ON IV.VendorID = V.VendorID AND
> IV.ItemID IN (1,
> 2, 3, 4, 5)
> GROUP BY V.VendorID, V.VendorName
> HAVING COUNT(IV.ItemID) = 5
>