SQL select with join are returning double results

Question

I am trying to select some data from different tables using join.

First, here is my SQL (MS) query:

SELECT Polls.pollID, 
       Members.membername, 
       Polls.polltitle, (SELECT COUNT(*) FROM PollChoices WHERE pollID=Polls.pollID) AS 'choices',
       (SELECT COUNT(*) FROM PollVotes WHERE PollVotes.pollChoiceID = PollChoices.pollChoicesID) AS 'votes'
FROM Polls
INNER JOIN Members
ON Polls.memberID = Members.memberID
INNER JOIN PollChoices
ON PollChoices.pollID = Polls.pollID;

And the tables involved in this query is here:

The query returns this result:

pollID   | membername | polltitle | choices | votes
---------+------------+-----------+---------+-------
10000036 | TestName   | Test Title|   2     |   0
10000036 | TestName   | Test Title|   2     |   1

Any help will be greatly appreciated.

Answer 1

Your INNER JOIN with PollChoices is bringing in more than 1 row for a given poll as there are 2 choices for the poll 10000036 as indicated by choices column.

You can change the query to use GROUP BY and get the counts.

In case you don't have entries for each member in the PollVotes or Polls table, you need to use LEFT JOIN

SELECT Polls.pollID, 
       Members.membername, 
       Polls.polltitle, 
       COUNT(PollChoices.pollID) as 'choices',
       COUNT(PollVotes.pollvoteId) as 'votes'
FROM Polls
INNER JOIN Members
ON Polls.memberID = Members.memberID
INNER JOIN PollChoices
ON PollChoices.pollID = Polls.pollID
INNER JOIN PollVotes
ON PollVotes.pollChoiceID = PollChoices.pollChoicesID
AND PollVotes.memberID = Members.memberID
GROUP BY Polls.pollID, 
         Members.membername, 
         Polls.polltitle