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 D092ED1B906 for ; Tue, 9 Mar 2004 14:58:45 +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 45956-09 for ; Tue, 9 Mar 2004 10:58:44 -0400 (AST) Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [192.108.102.143]) by svr1.postgresql.org (Postfix) with ESMTP id D03F4D1BA6C for ; Tue, 9 Mar 2004 10:58:35 -0400 (AST) Received: from [10.0.1.2] glaesema@smtp-send.myrealbox.com [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 3.56 $ on Novell NetWare; Tue, 09 Mar 2004 07:58:37 -0700 In-Reply-To: <042301c405db$b63c5340$2766f30a@development.greatgulfhomes.com> References: <042301c405db$b63c5340$2766f30a@development.greatgulfhomes.com> Mime-Version: 1.0 (Apple Message framework v612) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <3D3BDF3B-71DA-11D8-8694-000A95C88220@myrealbox.com> Content-Transfer-Encoding: 7bit Cc: , "'Matt Chatterley'" From: Michael Glaesemann Subject: Re: Trying to make efficient "all vendors who can provide all items" Date: Tue, 9 Mar 2004 23:58:35 +0900 To: X-Mailer: Apple Mail (2.612) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/83 X-Sequence-Number: 17182 Just one more to add to the pile. Got it from Celko's "SQL for Smarties", under the relational division section. I'd make a temporary table for the items on the list. CREATE TEMP TABLE select_items ( item_id foo NOT NULL UNIQUE REFERENCES items(item_id) ); SELECT DISTINCT vi1.vendor_id FROM vendors_items vi1 WHERE NOT EXISTS ( SELECT item_id FROM select_items EXCEPT SELECT items FROM vendors_items vi2 WHERE vi1.vendor_id = vi2.vendor_id ) ); Michael Glaesemann grzm myrealbox com On Mar 9, 2004, at 10:37 PM, wrote: > 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