>>>>First I get a list of discontinued products that have no stock or onorder values.
>>>>i.e.
>>>>

>>>>SELECT DISTINCT PRODUCT.ProductId,PRODUCT.ProdItem, PRODUCT.DESCRIPTION ;
>>>>	FROM PRODUCT INNER JOIN ProdSize ;
>>>>	ON PRODUCT.ProductId = ProdSize.PRODUCT ;
>>>>	WHERE PRODUCT.discontinued = .T. ;
>>>>	AND ProdSize.onorder = 0 ;
>>>>	AND ProdSize.stock = 0 ;
>>>>	ORDER BY ProdItem ;
>>>>	INTO CURSOR ProductsNoItems
>>>>

>>>>
>>>>Then I get the products that are discontinued that have stock or onorder values > 0.
>>>>i.e.
>>>>

>>>>SELECT DISTINCT PRODUCT.ProductId as ProdID;
>>>>	FROM PRODUCT INNER JOIN ProdSize ;
>>>>	ON PRODUCT.ProductId = ProdSize.PRODUCT ;
>>>>	WHERE PRODUCT.discontinued = .T. ;
>>>>	AND (ProdSize.onorder != 0 ;
>>>>	or ProdSize.stock != 0);
>>>>	INTO cursor ProductsWithItems
>>>>

>>>>
>>>>Now I'm stuck. How do I get the list of products to be deleted? i.e. If the product is in ProductsWithItems and ProdsNoItems it should NOT be deleted.
>>>>
>>>>TIA,
>>>>Mike
>>>
>>>You may try with SELECT first.
>>>
>>>Here is the DELETE command
>>>
>>>DELETE from Products p1 left join ProductsWithItems p2 on P1.ProductID = p2.ProductID left join ProductsNoItems p3 on p1.ProductID = p3.ProductID where p2.ProductID is NULL and p3.ProductID is null
>>>
>>>From the top of my head - not tested.
>>
>>Nope, didn't work... :(
>>
>>I can get it via a scan/endscan of the one table and delete matching records... but wouldn't an SQL select be faster...
>>
>>TIA,
>>Mike
>
>What exactly didn't work? Did you try a select first with the same criteria?
>
>Also you may do slightly different:
>
>delete from Products where ProductID not in (select ProductID from myFirstTable union select ProductID from mySecondtable)
>
>This looks easier to understand.
>
>BTW, did you want to delete matching records or non-matching records? I understood, that you want to leave only records which are in either of these two tables. Is it your goal or the opposite?

I want to remove any discontinued products that have no stock or any onorder. However since there are multiple children, I need to confirm that all of the children have no stock or products onorder. Only then will I remove a discontinued item.

Hope this helps,

TIA,
Mike