MYSQL Join Confusion

I 开发者_开发技巧have this Exams table and Responses table. I want to fetch all records where Exam for exam = 000663

SELECT COUNT(*)
  FROM Responses r  
  JOIN Exams E ON r.category_id_fk = E.id 
              AND r.exam_id_fk = 000663

There are actually total 106 rows only containing responses for exams 000663. But it returns all response rows which are for all exams! Shouldn't it consider r.exam_id_fk = 000663?

I did not use where clause cause I have 3 other tables joining to this query.

The 000663 condition belongs in a WHERE clause, rather than in the JOIN relationship conditions. Even if you have other tables joining against this (as a subquery), you can still use a WHERE clause.

select count(*)
from  Responses r  
join Exams E on r.category_id_fk = E.id 
WHERE r.exam_id_fk=000663;

Assuming E.id is the exame id and r.exam_id_fk is also the exam id try

select count(*)
from Exams e
left join Responses r on r.exam_id_fk = e.id
where e.id = 000663;

It appears you are joining on the exam id equaling a category id and your condition should be in the where clause

I am guessing that you are using incorrect column name. Use AND e.id = 000663 instead of AND r.exam_id_fk = 000663, so your query would work without a WHERE.

SELECT COUNT(*)
FROM Responses r  
JOIN Exams E ON r.category_id_fk = E.id 
AND e.id = 000663