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

Databases
1

Given this query:

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:

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:

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.

2

Add another join to get the number of moderations for each dogTag:

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

Thanks Guido, this is genius! Seems to work fine.