pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrying to make efficient "all vendors who can provide all items"
7+ messages / 4 participants
[nested] [flat]
* Trying to make efficient "all vendors who can provide all items"
@ 2004-03-08 16:02 terry@ashtonwoodshomes.com
2004-03-09 03:20 ` Re: Trying to make efficient "all vendors who can provide all items" Christopher Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 7+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-03-08 16:02 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
2004-03-08 16:02 Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
@ 2004-03-09 03:20 ` Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 7+ messages in thread
From: Christopher Browne @ 2004-03-09 03:20 UTC (permalink / raw)
To: pgsql-sql
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/;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
@ 2004-03-08 19:23 terry@ashtonwoodshomes.com
2004-03-08 20:40 ` Re: Trying to make efficient "all vendors who can provide all items" Matt Chatterley <zen31329@zen.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-03-08 19:23 UTC (permalink / raw)
To: 'Jeremy Semeiks' <jrs@denny.farviolet.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
2004-03-08 19:23 Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
@ 2004-03-08 20:40 ` Matt Chatterley <zen31329@zen.co.uk>
2004-03-08 20:45 ` Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-09 13:37 ` Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
0 siblings, 2 replies; 7+ messages in thread
From: Matt Chatterley @ 2004-03-08 20:40 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
2004-03-08 19:23 Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-08 20:40 ` Re: Trying to make efficient "all vendors who can provide all items" Matt Chatterley <zen31329@zen.co.uk>
@ 2004-03-08 20:45 ` terry@ashtonwoodshomes.com
1 sibling, 0 replies; 7+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-03-08 20:45 UTC (permalink / raw)
To: 'Matt Chatterley' <zen31329@zen.co.uk>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
2004-03-08 19:23 Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-08 20:40 ` Re: Trying to make efficient "all vendors who can provide all items" Matt Chatterley <zen31329@zen.co.uk>
@ 2004-03-09 13:37 ` terry@ashtonwoodshomes.com
2004-03-09 14:58 ` Re: Trying to make efficient "all vendors who can provide all items" Michael Glaesemann <grzm@myrealbox.com>
1 sibling, 1 reply; 7+ messages in thread
From: terry@ashtonwoodshomes.com @ 2004-03-09 13:37 UTC (permalink / raw)
To: 'Matt Chatterley' <zen31329@zen.co.uk>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Trying to make efficient "all vendors who can provide all items"
2004-03-08 19:23 Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-08 20:40 ` Re: Trying to make efficient "all vendors who can provide all items" Matt Chatterley <zen31329@zen.co.uk>
2004-03-09 13:37 ` Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
@ 2004-03-09 14:58 ` Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Glaesemann @ 2004-03-09 14:58 UTC (permalink / raw)
To: terry@ashtonwoodshomes.com; +Cc: pgsql-sql; 'Matt Chatterley' <zen31329@zen.co.uk>
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 UNIQUE
REFERENCES items(item_id)
);
SELECT DISTINCT vi1.vendor_id FROM vendors_items vi1
WHERE NOT EXISTS (
SELECT item_id FROM select_items
EXCEPT
SELECT items FROM vendors_items vi2
WHERE 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-03-09 14:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-08 16:02 Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-09 03:20 ` Christopher Browne <cbbrowne@acm.org>
2004-03-08 19:23 Re: Trying to make efficient "all vendors who can provide all items" terry@ashtonwoodshomes.com
2004-03-08 20:40 ` Matt Chatterley <zen31329@zen.co.uk>
2004-03-08 20:45 ` terry@ashtonwoodshomes.com
2004-03-09 13:37 ` terry@ashtonwoodshomes.com
2004-03-09 14:58 ` Michael Glaesemann <grzm@myrealbox.com>
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