show sql text »

SELECT c.ID, c.OBJID, c.NAME, c.NAME1, c.PARENTID, c.PARENTOBJID, c.DESCRIPTION,
au.NAME AUNAME, au.ID AUID, c.AYEAR, c.ABRACK, c.AYEARREM, c.ORIGGEN, c.ORIGSUBGEN,
gn.ID GNID, gn.NAME GNNAME, sgn.ID SGNID, sgn.NAME SGNNAME, c.RANK,
(SELECT COUNT(*) FROM classes c1 WHERE c1.PARENTOBJID=c.OBJID
) CNT,
(SELECT COUNT(*) FROM articles a1 WHERE a1.CLASSID=c.ID) PHOCNT
FROM classes c
LEFT JOIN c_author au ON au.ID=c.AUTHORID
LEFT JOIN classes gn ON gn.OBJID =
(CASE WHEN c.RANK IN (2,3,4,5)
THEN (SELECT t.POBJID
FROM classobj_tree t
WHERE t.OBJID=c.OBJID AND t.PRANK IN (7,8))
ELSE NULL
END)
LEFT JOIN classes sgn ON sgn.OBJID =
(CASE WHEN c.RANK IN (2,3,4,5)
THEN (SELECT t.POBJID
FROM classobj_tree t
WHERE t.OBJID=c.OBJID AND t.PRANK=6)
ELSE NULL
END)
WHERE 0=0 AND c.TAXON=1 AND (c.PARENTOBJID IS NULL OR c.PARENTOBJID = 0)
ORDER BY c.SORTORDER, c.RANK DESC, gn.NAME, sgn.NAME, c.NAME, c.NAME1, c.ID