X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id EEAB6D1DB90
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 16:04:12 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 35626-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 12:04:10 -0400 (AST)
Received: from mail.greatgulfhomes.com (unknown [216.94.125.243])
	by svr1.postgresql.org (Postfix) with ESMTP id 119F2D1DB6C
	for <pgsql-sql@postgresql.org>; Mon,  8 Mar 2004 12:04:10 -0400 (AST)
Received: from terrynew2 (firewall.development.greatgulfhomes.com
	[216.191.203.106])
	by mail.greatgulfhomes.com (Postfix) with SMTP id A640436696A
	for <pgsql-sql@postgresql.org>; Mon,  8 Mar 2004 11:07:57 -0500 (EST)
From: <terry@ashtonwoodshomes.com>
To: "Postgresql Sql Group (E-mail)" <pgsql-sql@postgresql.org>
Subject: Trying to make efficient "all vendors who can provide all items"
Date: Mon, 8 Mar 2004 11:02:13 -0500
Message-ID: <03e501c40526$c63d0380$2766f30a@development.greatgulfhomes.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/60
X-Sequence-Number: 17159

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?

Thanks



Terry Fielder
Manager Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
terry@greatgulfhomes.com
Fax: (416) 441-9085