Count the number of nodes with a status of 1
Group them by the community term they are associated with (term_node)
Limit the community terms to those in the community vocabulary (vid = 31 in term_data).
i think you were pretty close to that with your original query, except i think you want INNER joins rather than LEFT OUTER
SELECT td.name AS 'Community'
, COUNT(*) AS Count
FROM term_data td
INNER
JOIN term_node tn
ON tn.tid = td.tid
INNER
JOIN node n
ON n.nid = tn.nid
AND n.status = 1
WHERE td.vid = 31
GROUP
BY td.tid
ORDER
BY Count DESC;
if this still produces inflated counts, then you will have to explain the one-to-many relationships among your tables
Your term_node table is missing a PK. Maybe you’re having duplicate entries in there? That could explain the high numbers.
Putting a WHERE condition (different from IS [NOT] NULL) on a left joined table makes it behave as an INNER JOIN. Put those conditions in the ON.
Counting with left join has a problem: even if there aren’t any nodes with status 1 for a particular term, your query would still give 1 as a result, because there would be one row returned for that term, even if the node columns would be NULL.
Try with a subquery like this:
SELECT
td.name AS 'Community',
COALESCE(Count, 0) AS Count
FROM term_data td
LEFT JOIN
(SELECT tn.tid, COUNT(*) AS Count
FROM term_node tn
INNER JOIN node n
ON tn.nid = n.nid
WHERE n.status = 1
GROUP BY tn.tid
) AS a
ON a.tid = td.tid
WHERE td.vid = 31
ORDER BY Count DESC