CODEWITHSUNDEEP
mysqlsqldatabase
Marshall
Marshall

Reputation: 154

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

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. enter image description here

enter image description here

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;

enter image description here

Upvotes: 0

Views: 30

Answers (1)

Daniel W.
Daniel W.

Reputation: 32260

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;

Upvotes: 1

Related Questions