Irk, I, of course, forgot to join Company and Products, et al...
Oracle:
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
WHERE C.CompanyID=P.CompanyID
MINUS
SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S
WHERE C.CompanyID=S.CompanyID
SQL7:
SELECT C.CompanyID, P.Product ID
FROM Company C, Product P
WHERE WHERE C.CompanyID=P.CompanyID
AND NOT EXISTS
(SELECT C.CompanyID, S.Product ID
FROM Company C, StoreProducts S
WHERE C.CompanyID=S.CompanyID)
Edward Smith wrote:
>
> 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
