X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id B8BE2D1B530 for ; Mon, 8 Mar 2004 20:39:58 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 11291-03 for ; Mon, 8 Mar 2004 16:39:56 -0400 (AST) Received: from heisenberg.zen.co.uk (heisenberg.zen.co.uk [212.23.3.141]) by svr1.postgresql.org (Postfix) with ESMTP id D72AED1BB93 for ; Mon, 8 Mar 2004 16:39:51 -0400 (AST) Received: from [82.68.94.102] (helo=dorfl) by heisenberg.zen.co.uk with esmtp (Exim 4.30) id 1B0RXT-0003jc-3j; Mon, 08 Mar 2004 20:39:51 +0000 From: "Matt Chatterley" To: Cc: Subject: Re: Trying to make efficient "all vendors who can provide all items" Date: Mon, 8 Mar 2004 20:40:30 -0000 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Outlook, Build 11.0.5207 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 In-Reply-To: <009e01c40542$c6d14380$2766f30a@development.greatgulfhomes.com> Thread-Index: AcQFQ4lwCrvoVSi8TrWmHQw8V6dDuwACMBdA Message-ID: X-Originating-Heisenberg-IP: [82.68.94.102] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/63 X-Sequence-Number: 17162 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