>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.