I'm not entirely sure what you're trying to do, but try something like this
-
SELECT Products.product_ID
FROM Products
WHERE NOT EXISTS(
SELECT Product_ID
FROM StoreProducts
WHERE Product_ID = Products.Product_ID
AND Company_ID = Products.Company_ID
)
That should get all Products from the Products table without a matching
record in the storeProducts table.
Just a tip - I always try to write my complicated queries in plain English
first, then pseudocode, then translate it into SQL. I find it helps
tremendously, because once you've written exactly what you want in english,
the rest almost writes itself.
Hope that helps
Alistair Davidson
Senior Developer
Rocom New Media
www.rocomx.net
"A problem well stated is a problem half-solved"
Albert Einstein
-----Original Message-----
From: Duane Boudreau [mailto:[EMAIL PROTECTED]]
Sent: 04 April 2001 16:35
To: CF-Talk
Subject: SQL: Help!
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
