SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can't figure out Join Syntax

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

    Code MySQL:
    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)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please explain in words what you are counting
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code MySQL:
    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)

  4. #4
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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).

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think you were pretty close to that with your original query, except i think you want INNER joins rather than LEFT OUTER
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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:
    Code:
    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

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thank you!

    Thank you r937 and guido2004!

    Both of your approaches give me the numbers I need.

    I truly appreciate your help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •