Thanks for the quick answer - sorry for the slow reply. Works like a charm.
Duane
-----Original Message-----
From: Edward Smith [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, April 04, 2001 12:07 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: Re: SQL: Help!
Database?
If it is Oracle, I might use the "MINUS" Operator:
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
MINUS
SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S
To get Company->Product Uniques
And swap the two queries to get StoreProducts uniques.
Not sure if an analogous operater exists in TransactSQL.. Yep, its NOT
EXISTS
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
WHERE NOT EXISTS
(SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S)
Access? Not sure...
Duane Boudreau wrote:
>
> I've got one heckuva mess on my hands. I am trying to straighten out a
huge
> mess of an ecommerce application I have inherited. It went into production
> w/h zero testing a couple of months ago and now I am in firefighting
mode).
>
> This app relies heavily on three tables company, products, storeproducts.
> Company contains store retailers and product distributors, products
contain
> all products entered by distributors and storeproducts is a derived table
> that contains the combinations of products to retailers. (confused
yet???).
> My problem is that I need to write a routine that lists all the
combinations
> that exist in the company -> products that do not exist in storeproducts
and
> all the combinations in storeproducts that do not exist in company ->
> products. A simplified version of the db schema looks like?:
>
> Company
> Company_ID (retailer or distributor)
>
> Products
> Company_ID (distributor)
> Product_ID
>
> StoreProducts
> Company_ID (retailer)
> Product_ID
>
> The answer probably uses outer joins but I haven't yet mastered the art of
> writting outer joins.
>
> Duane
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
