Saturday, 17 August 2013

Query "on" clause not limiting join?

Query "on" clause not limiting join?

I have the query as below, what it is meant to do is
Return id of all submissions into competition 2 including a count of how
many times they have been read.
Which it is doing everything fine, except it's returning rows from all
other competitions as well; Why is it not only joining where
`competition_id` = 2?
SELECT
`c`.`competition_id`,
`c`.`submission_id`,
COUNT(
`submission_reads`.`submission_id`
) `reads`
FROM
`submission_reads`
RIGHT JOIN `competition_submissions` c
ON `c`.`submission_id` = `submission_reads`.`submission_id`
AND c.top_round = 1
AND c.`competition_id` = 2
GROUP BY `c`.`submission_id`

No comments:

Post a Comment