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