Mine

SELECT P1.xar_cid, COUNT(P2.xar_cid) AS numitems
              FROM xar_comments AS P1,
                   xar_comments AS P2
             WHERE (
                         P1.xar_modid    = P2.xar_modid
                     AND P1.xar_objectid = P2.xar_objectid
                     AND P1.xar_itemtype = P2.xar_itemtype
                   )
               AND P2.xar_left >= P1.xar_left
               AND P2.xar_left <= P1.xar_right
               AND P1.xar_left >= 46
               AND P1.xar_right <= 47
               AND P2.xar_status='2'
          GROUP BY P1.xar_cid;