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

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

From

Date:

08 March 2004, 15:04:14

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

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

From

Date:

08 March 2004, 18:24:39

Thanks for your response.

If I understand your proposal, it is a way of getting vendors who can
provide me with all the items in the items table.

But the situation I have is items table could have 100k items, and I want
all vendors who can provide a specific list of say 20 items.

Do I misunderstand your query?

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


> -----Original Message-----
> From: Jeremy Semeiks [mailto:jrs@denny.farviolet.com]
> Sent: Monday, March 08, 2004 2:07 PM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Trying to make efficient "all vendors who
> can provide
> all items"
>
>
> On Mon, Mar 08, 2004 at 11:02:13AM -0500,
> terry@ashtonwoodshomes.com wrote:
> > 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
> [...]
> > 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?
>
> You could use some subselects:
>
> select vendor_id from
> (select vendor_id, count(*) as ct from item_vendors group by
> vendor_id) vict
> where ct = (select count(*) from items);
>
> I haven't tested this.
>
> - Jeremy
>

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

From

"Matt Chatterley"

Date:

08 March 2004, 19:40:01

Hmm. My PGSQL knowledge is rusty, so this may be slightly microsoftified..

How about just:

SELECT V.VendorID, V.VendorName, COUNT(IV.ItemID)
FROM Vendor V
INNER JOIN Item_Vendor IV ON IV.VendorID = V.VendorID AND IV.ItemID IN (1,
2, 3, 4, 5)
GROUP BY V.VendorID, V.VendorName
HAVING COUNT(IV.ItemID) = 5

Alternatively, you could repeatedly inner join onto Item_vendor, but this is
no 'nicer' than either of your previous solutions:

SELECT V.VendorID, V.VendorName
FROM Vendor V
INNER JOIN Item_Vendor IV1 ON IV1.VendorID = V.VendorID AND IV1.ItemID = 1
INNER JOIN Item_Vendor IV2 ON IV2.VendorID = V.VendorID AND IV2.ItemID = 2
INNER JOIN Item_Vendor IV3 ON IV3.VendorID = V.VendorID AND IV3.ItemID = 3
INNER JOIN Item_Vendor IV4 ON IV4.VendorID = V.VendorID AND IV4.ItemID = 4
INNER JOIN Item_Vendor IV5 ON IV5.VendorID = V.VendorID AND IV5.ItemID = 5


Does that help at all, or am I barking up the wrong tree? :)


Regards,


Matt.

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of terry@ashtonwoodshomes.com
Sent: 08 March 2004 19:23
To: 'Jeremy Semeiks'
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Trying to make efficient "all vendors who can provide all
items"

Thanks for your response.

If I understand your proposal, it is a way of getting vendors who can
provide me with all the items in the items table.

But the situation I have is items table could have 100k items, and I want
all vendors who can provide a specific list of say 20 items.

Do I misunderstand your query?

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


> -----Original Message-----
> From: Jeremy Semeiks [mailto:jrs@denny.farviolet.com]
> Sent: Monday, March 08, 2004 2:07 PM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Trying to make efficient "all vendors who
> can provide
> all items"
>
>
> On Mon, Mar 08, 2004 at 11:02:13AM -0500,
> terry@ashtonwoodshomes.com wrote:
> > 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
> [...]
> > 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?
>
> You could use some subselects:
>
> select vendor_id from
> (select vendor_id, count(*) as ct from item_vendors group by
> vendor_id) vict
> where ct = (select count(*) from items);
>
> I haven't tested this.
>
> - Jeremy
>


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

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

From

Date:

08 March 2004, 19:46:53

That's pretty nifty code.  It certainly looks nicer, and looks like it would
work providing vendor_id&item_id is the pk of item_vendors (and it is).  I
will let you know if it runs any faster...

Thanks

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


> -----Original Message-----
> From: Matt Chatterley [mailto:zen31329@zen.co.uk]
> Sent: Monday, March 08, 2004 3:41 PM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: RE: [SQL] Trying to make efficient "all vendors who
> can provide
> all items"
>
>
> Hmm. My PGSQL knowledge is rusty, so this may be slightly
> microsoftified..
>
> How about just:
>
> SELECT V.VendorID, V.VendorName, COUNT(IV.ItemID)
> FROM Vendor V
> INNER JOIN Item_Vendor IV ON IV.VendorID = V.VendorID AND
> IV.ItemID IN (1,
> 2, 3, 4, 5)
> GROUP BY V.VendorID, V.VendorName
> HAVING COUNT(IV.ItemID) = 5
>

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

From

Christopher Browne

Date:

09 March 2004, 02:52:00

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
havingcount(*) = 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/>

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

From

Date:

09 March 2004, 12:39:09

Of all the proposed solutions, this appears to run the fastest, and not
require the creation of an additional table.

Thanks!

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


> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Matt Chatterley
> Sent: Monday, March 08, 2004 3:41 PM
> To: terry@ashtonwoodshomes.com
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Trying to make efficient "all vendors who
> can provide
> all items"
>
>
> Hmm. My PGSQL knowledge is rusty, so this may be slightly
> microsoftified..
>
> How about just:
>
> SELECT V.VendorID, V.VendorName, COUNT(IV.ItemID)
> FROM Vendor V
> INNER JOIN Item_Vendor IV ON IV.VendorID = V.VendorID AND
> IV.ItemID IN (1,
> 2, 3, 4, 5)
> GROUP BY V.VendorID, V.VendorName
> HAVING COUNT(IV.ItemID) = 5
>

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

From

Michael Glaesemann

Date:

09 March 2004, 13:58:47

Just one more to add to the pile. Got it from Celko's "SQL for 
Smarties", under the relational division section.

I'd make a temporary table for the items on the list.

CREATE TEMP TABLE select_items (item_id foo NOT NULL UNIQUEREFERENCES items(item_id)
);

SELECT DISTINCT vi1.vendor_id FROM vendors_items vi1
WHERE NOT EXISTS (SELECT item_id FROM select_itemsEXCEPTSELECT items FROM vendors_items vi2WHERE vi1.vendor_id =
vi2.vendor_id)
);

Michael Glaesemann
grzm myrealbox com

On Mar 9, 2004, at 10:37 PM, <terry@ashtonwoodshomes.com> wrote:

> Of all the proposed solutions, this appears to run the fastest, and not
> require the creation of an additional table.
>
> Thanks!
>
> Terry Fielder
> Manager Software Development and Deployment
> Great Gulf Homes / Ashton Woods Homes
> terry@greatgulfhomes.com
> Fax: (416) 441-9085
>
>
>> -----Original Message-----
>> From: pgsql-sql-owner@postgresql.org
>> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Matt Chatterley
>> Sent: Monday, March 08, 2004 3:41 PM
>> To: terry@ashtonwoodshomes.com
>> Cc: pgsql-sql@postgresql.org
>> Subject: Re: [SQL] Trying to make efficient "all vendors who
>> can provide
>> all items"
>>
>>
>> Hmm. My PGSQL knowledge is rusty, so this may be slightly
>> microsoftified..
>>
>> How about just:
>>
>> SELECT V.VendorID, V.VendorName, COUNT(IV.ItemID)
>> FROM Vendor V
>> INNER JOIN Item_Vendor IV ON IV.VendorID = V.VendorID AND
>> IV.ItemID IN (1,
>> 2, 3, 4, 5)
>> GROUP BY V.VendorID, V.VendorName
>> HAVING COUNT(IV.ItemID) = 5