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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: terry@ashtonwoodshomes.com
Subject: Re: Trying to make efficient "all vendors who can provide all items"
In-Reply-To: <03e501c40526$c63d0380$2766f30a@development.greatgulfhomes.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox