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

Поиск
Список
Период
Сортировка
От
Тема Trying to make efficient "all vendors who can provide all items"
Дата
Msg-id 03e501c40526$c63d0380$2766f30a@development.greatgulfhomes.com
обсуждение исходный текст
Список pgsql-sql
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
ANDitems_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



В списке pgsql-sql по дате отправления:

Предыдущее
От: Daniel Henrique Alves Lima
Дата:
Сообщение: Re: Functional index and string concatenation
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Functional index and string concatenation