>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.
If it's not broken, fix it until it is.
My Blog