From: Christopher Browne <cbbrowne@acm.org>
Subject: Re: Trying to make efficient "all vendors who can provide all items"
Date: Mon, 08 Mar 2004 22:20:03 -0500
Organization: cbbrowne Computing Inc
Lines: 74
Message-ID: <m3ad2qn1uk.fsf@wolfe.cbbrowne.com>
References: <03e501c40526$c63d0380$2766f30a@development.greatgulfhomes.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through
 Obscurity,
	linux)
Cancel-Lock: sha1:xz3blTNsKGcAxgfuSTa1hwOADNs=
To: pgsql-sql@postgresql.org

Clinging to sanity, terry@ashtonwoodshomes.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/>