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
