That's pretty nifty code. It certainly looks nicer, and looks like it would work providing vendor_id&item_id is the pk of item_vendors (and it is). I will let you know if it runs any faster...
Thanks Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -----Original Message----- > From: Matt Chatterley [mailto:[EMAIL PROTECTED] > 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
