From: Michael Glaesemann <grzm@myrealbox.com>
To: terry@ashtonwoodshomes.com
Cc: pgsql-sql@postgresql.org
Cc: 'Matt Chatterley' <zen31329@zen.co.uk>
Subject: Re: Trying to make efficient "all vendors who can provide all items"
Date: Tue, 9 Mar 2004 23:58:35 +0900
Message-ID: <3D3BDF3B-71DA-11D8-8694-000A95C88220@myrealbox.com> (raw)
In-Reply-To: <042301c405db$b63c5340$2766f30a@development.greatgulfhomes.com>
References: <042301c405db$b63c5340$2766f30a@development.greatgulfhomes.com>
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
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: grzm@myrealbox.com, terry@ashtonwoodshomes.com, zen31329@zen.co.uk
Subject: Re: Trying to make efficient "all vendors who can provide all items"
In-Reply-To: <3D3BDF3B-71DA-11D8-8694-000A95C88220@myrealbox.com>
* 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