Does a database query count all yes / no votes and the db record to which they belong?

Question

I have two tables, one for articles / polls and one for votes on these articles. Votes are held in the enumeration field to increase / decrease and a couple of other options. I am trying to create a query that returns the data I need for an article, and also give me the sum of all the votes. Unfortunately, I get an unexpected result. This is a count of all my votes, as if they were cast for the first recording, and not for anyone else.

How can I correctly associate my votes with the polls that they belong to?

tblVotes
[PK] primaryId  |    voterId (FK)  | voteValue (enum) |    postID (fk)
----------------------------------------------------------------------
     1                    10                 up              1
     2                    11                 down            1
     3                    11                 up              10

tblContent
[PK] unique Id |   postTitle   |   postBody  |   postAuthor(FK)  |
------------------------------------------------------------------
       1               foo           foofoo          12
      10               bar           barbar          10
      11               foobar    foofoobarbar        10

When I customize the query, described in detail below, I expect to get these results:

uniqueId |userName | pollDate | postTitle  |postBody  | upVotes| downVotes
--------------------------------------------------------------------------
   1         bob      1/1/11      foo        foofoo      1         1
  10         john     1/2/11      bar        barbar      1         null
  11         john     1/3/11      foofoo     foofoobar.. null      null

Unfortunately, I really get the following results:

uniqueId |userName | pollDate | postTitle  |postBody  | upVotes| downVotes
--------------------------------------------------------------------------
   1         bob      1/1/11      foo        foofoo      2         1
  10         john     1/2/11      bar        barbar      null      null
  11         john     1/3/11      foofoo     foofoobar.. null      null

Here is my SQL statement:

            SELECT
                tblContent.uniqueID,
                tblUsers.userName,
                tblContent.postDate,
                tblContent.postTitle,
                tblContent.postBody,
                votes.upVotes,
                votes.downVotes
            FROM
                tblContent
                    LEFT JOIN
                    (
                        SELECT
                            postId,
                            SUM(CASE WHEN tblVotes.voteType = 'up' THEN 1 ELSE 0 END) as upVotes,
                            SUM(CASE WHEN tblVotes.voteType = 'down' THEN 1 ELSE 0 END) as downVotes
                        FROM
                            tblVotes, tblContent
                        WHERE
                            tblContent.uniqueId = tblVotes.postId
                     ) votes
                    ON tblContent.uniqueId = votes.postId
                    LEFT JOIN
                        tblUsers
                    ON tblUsers.userId = tblContent.postAuthor
            WHERE
                postApproved = true
            ORDER BY
                postDate DESC

Answer 1

GROUP BY SQL.

Answer 2

:

   SELECT p.title,
          SUM(CASE WHEN v.votevalue = 'yes' THEN 1 ELSE 0 END) AS yesCount,
          SUM(CASE WHEN v.votevalue = 'no' THEN 1 ELSE 0 END) AS noCount
     FROM POLLS p
LEFT JOIN VOTES v ON v.pollid = p.uniqueid
 GROUP BY p.title