>>I have a parent tale called products. One of the fields is discontinued.
>>
>>I have a child table, that links to the parent products via the productid key field. The child table has both an onorder and a stock field, to store any items that are onorder or have stock in inventory.
>>
>>There is a one to many relationship with the child.
>>
>>Now, I want to allow the user to archive discontinued items that do not have any records in the child that has stock or onorder values >0.
>>
>>I am doing it thusly:
>>
>>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