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

[Michael Glaesemann]

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, <[EMAIL PROTECTED]> 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
[EMAIL PROTECTED]
Fax: (416) 441-9085

-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Matt Chatterley
Sent: Monday, March 08, 2004 3:41 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend