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

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
terry(at)greatgulfhomes(dot)com
Fax: (416) 441-9085

> -----Original Message-----
> From: Jeremy Semeiks [mailto:jrs(at)denny(dot)farviolet(dot)com]
> Sent: Monday, March 08, 2004 2:07 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"
>
>
> On Mon, Mar 08, 2004 at 11:02:13AM -0500,
> terry(at)ashtonwoodshomes(dot)com 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
>