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

Clinging to sanity, terry(at)ashtonwoodshomes(dot)com mumbled into her beard:
> 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
>
> Solution 1:
> SELECT vendor_id
> FROM vendors
> WHERE EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_1')
> AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_2')
> AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_3')
> AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_4')
> AND EXISTS (SELECT 1 FROM item_vendors WHERE vendors.vendor_id =
> item_vendors.vendor_id AND item_id = 'item_5')
>
> Solution 2:
> SELECT vendors.vendor_id
> FROM vendors, items AS item_1, items AS item_2, items AS item_3, items AS
> item_4, items AS item_5
> WHERE items_1.vendor_id = vendors.vendor_id AND items_1.item_id = 'item_1'
> AND items_2.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_2'
> AND items_3.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_3'
> AND items_4.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_4'
> AND items_5.vendor_id = vendors.vendor_id AND items_2.item_id = 'item_5'
>
> 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?

I'd suggest putting in another table containing the items that you
want to check against...

create table list_items (
item_id text not null unique
);
insert into list_items (item_id) values ('item_1');
insert into list_items (item_id) values ('item_2');
insert into list_items (item_id) values ('item_3');
insert into list_items (item_id) values ('item_4');
insert into list_items (item_id) values ('item_5');

select v.vendor_id, v.vendor_name from
vendors v,
(select vendor_id, count(*) from
list_items l, item_vendors iv where
iv.item_id = l.item_id
group by vendor_id
having count(*) = 5) as vendors_sat
where v.vendor_id = vendors_sat.vendor_id;

Extend it to 20, and the query only need change "5" to "20"...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://www.ntlug.org/~cbbrowne/unix.html
Rules of the Evil Overlord #56. "My Legions of Terror will be trained
in basic marksmanship. Any who cannot learn to hit a man-sized target
at 10 meters will be used for target practice."
<http://www.eviloverlord.com/>