there is a script on this site for deleting widows/orphans


------------
mark h at 5/9/01 8:55:35 AM


Hello,

this is driving me mad. I have a product groups and items database. Each record has an id, parentid and threadid. It is possible to nest groups and items e.g. groups are electrical, video, nicam with say 5 products in each category and the group "video" having two sub-groups nicam and mono.

If I delet the group video, how do I disable or delete all the "children" of this group? The threadid is the id of the first group, parentID is the id of the group within which the next group/ products are nested. I've tried a few joins, which gets me the first orphaned group but not any further, as below.

SELECT P1.id, P1.pid, P2.id, P2.pid
FROM prod_cat AS P1 RIGHT JOIN prod_cat AS P2 ON P1.id = P2.pid
WHERE (((P1.pid) Is Null) AND ((P2.pid)<>0));


Any suggestions or examples very welcome.

thanks,

Mark