WHERE clause with JOIN SQL-CodePudding

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

CodePudding user response：

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;