Mysql - Inner Join problem, output appears more times than needed-CodePudding

I have a problem. I'm trying to get all authors which have exactly one post. It works quite good, but, it shows me the same output exactly 3 times more.

Query:

select a.name as 'Name', a.surname as 'Surname' from author as a inner join post as p on (select count(body) from post where authorID = a.ID) = 1;

CodePudding user response：

You are getting a result for each post, instead of for each author. This is because you join the inner query that fetches from post.

I'd use the inbuilt aggregate tokens to get the desired output:

SELECT DISTINCT a.ID, a.name AS 'Name', a.surname AS 'Surname'
FROM author a
JOIN post p
ON p.authorID = a.ID
GROUP BY p.authorID
HAVING COUNT(p.body) = 1;