Can't figure out Join Syntax

[kmillecam]

I'm trying to limit my results to nodes with status = 1 but I can tell that that part of my query is being ignored (numbers are much too large).

mysql> SELECT td.name AS 'Community', COUNT(*) AS Count 
    -> FROM term_data td
    -> LEFT JOIN term_node tn
    -> ON tn.tid = td.tid
    -> LEFT OUTER JOIN node n
    -> ON tn.nid = n.nid
    -> WHERE td.vid = 31 AND n.status = 1
    -> GROUP BY tn.tid 
    -> ORDER BY Count DESC;
+-------------------------+-------+
| Community               | Count |
+-------------------------+-------+
| Backup and Archiving    | 87909 | 
| Security                | 38130 | 
| Endpoint Management     | 33074 | 
| Storage and Clustering  |  5699 | 
| Developers              |  3906 | 
| Endpoint Virtualization |  2512 | 
| Inside Symantec         |  2338 | 
| Vision User Conference  |   758 | 
| Partners                |   733 | 
+-------------------------+-------+
9 rows in set (1.63 sec)

[r937]

please explain in words what you are counting

[kmillecam]

Count the number of nodes with status = 1.
That are tagged with terms from term_node.
That are part of the vocabulary 31 as defined in term_data.

mysql> describe node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| type      | varchar(32)      | NO   | MUL |         |                | 
| title     | varchar(255)     | NO   | MUL |         |                | 
| uid       | int(10)          | NO   | MUL | 0       |                | 
| status    | int(4)           | NO   | MUL | 1       |                | 
| created   | int(11)          | NO   | MUL | 0       |                | 
| changed   | int(11)          | NO   | MUL | 0       |                | 
| comment   | int(2)           | NO   |     | 0       |                | 
| promote   | int(2)           | NO   | MUL | 0       |                | 
| moderate  | int(2)           | NO   | MUL | 0       |                | 
| sticky    | int(2)           | NO   |     | 0       |                | 
| vid       | int(10) unsigned | NO   | UNI | 0       |                | 
| language  | varchar(12)      | NO   |     |         |                | 
| tnid      | int(10) unsigned | NO   | MUL | 0       |                | 
| translate | int(11)          | NO   | MUL | 0       |                | 
+-----------+------------------+------+-----+---------+----------------+
15 rows in set (0.01 sec)
 
mysql> describe term_node;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| nid   | int(10) unsigned | NO   | MUL | 0       |       | 
| tid   | int(10) unsigned | NO   | MUL | 0       |       | 
| vid   | int(10) unsigned | NO   | MUL | 0       |       | 
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
 
mysql> describe term_data;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| tid         | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
| vid         | int(10) unsigned | NO   | MUL | 0       |                | 
| name        | varchar(255)     | NO   |     |         |                | 
| description | varchar(255)     | NO   |     | NULL    |                | 
| weight      | tinyint(4)       | NO   |     | 0       |                | 
| language    | varchar(12)      | NO   |     |         |                | 
| trid        | int(11)          | NO   |     | 0       |                | 
+-------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

[kmillecam]

Let's try that English description again

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).

[r937]

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

[guido2004]

1) Your term_node table is missing a PK. Maybe you're having duplicate entries in there? That could explain the high numbers.

2) 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.

3) 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

[kmillecam]

Thank you r937 and guido2004!

Both of your approaches give me the numbers I need.

I truly appreciate your help.