Обсуждение: Trying to make efficient "all vendors who can provide all items"
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
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 >
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
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 >
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/>
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 >
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