From: Matt Chatterley <zen31329@zen.co.uk>
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Trying to make efficient "all vendors who can provide all items"
Date: Mon, 8 Mar 2004 20:40:30 -0000
Message-ID: <Zen-1B0RXT-0003jc-3j@heisenberg.zen.co.uk> (raw)
In-Reply-To: <009e01c40542$c6d14380$2766f30a@development.greatgulfhomes.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zen31329@zen.co.uk, terry@ashtonwoodshomes.com
Subject: Re: Trying to make efficient "all vendors who can provide all items"
In-Reply-To: <Zen-1B0RXT-0003jc-3j@heisenberg.zen.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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