>I want to write a query that will find all product IDs in the product
table, that are not found in either the owner OR the
>sub_object columns in the menu table.
>(looking for orphan records, that are not found anywhere in the menu
table).
>
>Any suggestions, here is what I have so far!
>
> SELECT f.sub_object, p.title, p.sku
> FROM menu_table f, product_table p
> WHERE p.id = f.sub_object
> OR p.id = f.owner
> AND f.type = 'product'
> AND f.sub_object NOT IN ('p.id')
> AND f.owner NOT IN ('p.id')
Try this:
SELECT p.id, p.title
FROM product_table p
WHERE p.id NOT IN (SELECT DISTINCT f1.sub_object
FROM menu_table f1)
AND p.id NOT IN (SELECT DISTINCT f2.owner
FROM menu_table f2)
--
Al Everett
Director, Project Operations
Innercrossing Technology Group
www.innercrossing.com
[EMAIL PROTECTED]
Office: (908) 497-9004
Fax: (908) 497-9003
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
