WHERE clause with JOIN SQL

Question

SELECT  AVG(score) AS avg_score, st.name
FROM firstTable AS ft
LEFT JOIN secondTable AS st
ON ft.dog_id = st.dog_id
WHERE (SELECT COUNT(ft.dog_id) FROM firstTable) > 1
GROUP BY dog_id

The where clause doesnt seem to do anything. Why is that? - I'm essentially trying to output the average score only to the dogs that appear more than once in the first table

Answer 1

You should use an INNER join since you want only dogs that match in both tables and add the condition in the HAVING clause:

SELECT AVG(ft.score) AS avg_score, st.name
FROM secondTable AS st INNER JOIN firstTable AS ft
ON ft.dog_id = st.dog_id
GROUP BY st.dog_id
HAVING COUNT(*) > 1;