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 UNIQUEREFERENCES items(item_id)
);
SELECT DISTINCT vi1.vendor_id FROM vendors_items vi1
WHERE NOT EXISTS (SELECT item_id FROM select_itemsEXCEPTSELECT items FROM vendors_items vi2WHERE vi1.vendor_id =
vi2.vendor_id)
);
Michael Glaesemann
grzm myrealbox com
On Mar 9, 2004, at 10:37 PM, <terry@ashtonwoodshomes.com> 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