SQL based orphan list

First, the trick:

SELECT DISTINCT c.*,n.title
FROM (
  category c
  INNER JOIN node n
    ON c.cid=n.nid
  INNER JOIN category_hierarchy p2
    ON c.cid=p2.cid

  LEFT OUTER JOIN category_hierarchy p1
    ON p1.cid = p2.parent
)
WHERE p2.parent <> 0
AND p1.cid IS NULL;

This code implements a set difference between (all categories in the hierarchy) and (categories in the hierarchy with parents that exist in the hierarchy) to get (categories in the hierarchy with parents that do not exist in the hierarchy.)

• It is db_rewrite_sql compatible. (Tested on mysql 4.1.x with simple_access.)
• It only needs to do a single table scan

This code will not find completely uncategorized cats (as opposed to normal orphans, which are still categorized, but with invalid parents), but it should be possible to do this with another query that finds the set of categories that don't have any mention in the category_hierarchy table.

--Brandon