SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Mysql count rows in a JOIN table statement, is it possible?

    Given this query:

    Code MySQL:
    SELECT DISTINCT a.dogTag, a.dateEdited
    FROM dvdpedia a
    JOIN dvd_uploads b ON a.dogTag = b.dogTag
    JOIN dvd_moderators c ON a.dogTag = c.dogTag
    WHERE b.uploader != 9 AND c.moderator != 9
    ORDER BY a.dogTag ASC
    LIMIT 50;
    I only want to select the rows from dvd_moderators that appear only once, that is that have been moderated once only and not more and that don't match the userID given (9 in this example).dvd_moderators would look something like this:

    Code MySQL:
    id   dogTag   moderator
    219    2           9
    2226  2           7
    Of course the moderator ID would change for other entries.Using a COUNT works mostly except when the userID matches the moderator:

    Code MySQL:
    SELECT a.dogTag, a.dateEdited, COUNT(c.dogTag) AS moderations
    FROM dvdpedia a
    JOIN dvd_uploads b ON a.dogTag = b.dogTag
    JOIN dvd_moderators c ON a.dogTag = c.dogTag
    WHERE 1=1 AND b.uploader != 9 AND c.moderator != 9
    GROUP BY c.dogTag
    HAVING moderations = 1 
    ORDER BY a.dogTag ASC
    LIMIT 50;

    Then it reports it as being moderated only once (which is a correct query, it is discarding the row that matches moderator = 9 and hence the count is coming out to 1 instead of 2) but incorrect for the purposes I need.

    Basically I want the rows that have been moderated only once and that one of those moderators does not match the userID (9 in the example above).

    How could I achieve this?
    Thanks.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Add another join to get the number of moderations for each dogTag:
    Code:
    SELECT a.dogTag, a.dateEdited
    FROM dvdpedia a
    JOIN dvd_uploads b ON a.dogTag = b.dogTag
    JOIN dvd_moderators c ON a.dogTag = c.dogTag
    JOIN
      (SELECT dogTag
       FROM dvd_moderators
       GROUP by dogTag
       HAVING count(dogTag) = 1
      ) as d
    ON a.dogTag = d.dogTag
    WHERE 1=1 
    AND   b.uploader != 9 
    AND   c.moderator != 9
    ORDER BY a.dogTag ASC
    LIMIT 50;

  3. #3
    SitePoint Evangelist
    Join Date
    May 2002
    Location
    Barcelona, Spain
    Posts
    434
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Guido, this is genius! Seems to work fine.
    Used to prefer PHP.
    Now I prefer Maya but stuck with PHP
    bruji.com


Tags for this Thread

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
  •