Squidster

SELECT DISTINCT td.tid, td.name, (
    SELECT count( 1 )
    FROM term_node tn1
    LEFT JOIN term_node tn2 ON tn1.nid = tn2.nid
    LEFT JOIN term_data td2 ON td2.tid = tn2.tid
    WHERE tn1.tid = td.tid
       AND td2.vid =4
    ) num_nodes_with_screencasts,
    (
        SELECT DISTINCT count(n.title) 
        FROM node n 
        LEFT JOIN term_node tn ON n.nid = tn.tid 
        WHERE n.type = 'lesson' 
          AND tn.tid = td.tid
    ) is_lesson
FROM term_data td
WHERE td.vid =1
ORDER BY td.name