Re: Trying to make efficient "all vendors who can provide all items"

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, <terry(at)ashtonwoodshomes(dot)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(at)greatgulfhomes(dot)com
> Fax: (416) 441-9085
>
>
>> -----Original Message-----
>> From: pgsql-sql-owner(at)postgresql(dot)org
>> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Matt Chatterley
>> Sent: Monday, March 08, 2004 3:41 PM
>> To: terry(at)ashtonwoodshomes(dot)com
>> Cc: pgsql-sql(at)postgresql(dot)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