Thanks for your response. If I understand your proposal, it is a way of getting vendors who can provide me with all the items in the items table.
But the situation I have is items table could have 100k items, and I want all vendors who can provide a specific list of say 20 items. Do I misunderstand your query? Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -----Original Message----- > From: Jeremy Semeiks [mailto:[EMAIL PROTECTED] > Sent: Monday, March 08, 2004 2:07 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Trying to make efficient "all vendors who > can provide > all items" > > > On Mon, Mar 08, 2004 at 11:02:13AM -0500, > [EMAIL PROTECTED] wrote: > > My mind is drawing a blank. Please consider: > > TABLE 1: items: list of items in catalog > > item_id | item_description > > > > TABLE 2: vendors: list of vendors who provide 1 or more items > > vendor_id | vendor_name > > > > TABLE 3: item_vendors: record existence indicates vendor > can provide item > > item_id | vendor_id > > > > > > QUESTION: > > I have a list of say 5 items, and I want to find all > vendors who can provide > > ALL 5 items > [...] > > Yep, both my solutions are pretty ugly, especially in > situations where my > > list of items that need to be provided grow large. > > > > There must be a better way. Can anyone help me with this? > > You could use some subselects: > > select vendor_id from > (select vendor_id, count(*) as ct from item_vendors group by > vendor_id) vict > where ct = (select count(*) from items); > > I haven't tested this. > > - Jeremy > ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
