SQL Server WHERE clause when duplicates

I have a tables of blog posts - with each post given a score. Users can vote on the post which will either increment or decrement the score.

I only want users to be able to vote once on a post, so I have a votes table with the userId and postId. When the user attempts to vote, it first checks to see if they have already voted. They can't vote again, but they can 'unvote' or reverse their vote. Similar to SO.

Now, that all works fine, however when the page is displayed, I want to show users what posts they have voted on. Like SO shows you your votes.

I select the posts from the post table, and left outer join on the votes table so I can check if a user has voted.

SELECT * FROM (    
    SELECT row_number() over ( ORDER BY CreationDate DESC) AS rn, *
    FROM (
    SELECT p.id, ownerUserId, p.CreationDate, COUNT( p.id ) over ( partition BY NULL ) AS totalRecordCount, v.voteType
        FROM Posts p
    LEFT OUTER JOIN Votes v on p.id = v.postId
    ) x
) y
WHERE
rn BETWEEN 1 AND 20

This now gives me the posts, but they contain duplicate entries for every vote. I can't have this because it breaks my pagination.

I tried adding:

WHERE (v.userId = 1234 OR v.userId IS NULL)

which doesn't really work because obviously this wouldn't show a post that the current user hasn't voted on, but someone else has.

Do you know how I can achieve this? Can it be done in one query?

EDIT:

The above query is a cut down version. Basically the query returns the posts. That's it. I then display a voteUpButton and voteDownButton on the page, which users use to vote. What I want is to check if a user has voted on a post and show that vote by making the button bigger.

So where I display the button, I want to do something like :

 if (session.userObj.isLoggedIn() && len(postBoObj.get("voteType")) && postBoObj.get("voteType") == upVote) {
     //show big up button
else
    //show normal up button

The voteType is stored in the votes table, so when I joined the post table and votes table, and added my "WHERE (v.userId = 1234 OR v.userId IS NULL)" I could assume that if voteType existed then the current logged in user had voted on that post. But as I mentioned this will only show the posts that th开发者_C百科e user has voted on, or no user has voted on. If there is post that another user has voted on then the "WHERE (v.userId = 1234 OR v.userId IS NULL)" would exclude it and the post would never be seen.

I hope I have explained myself ok. I basically want exactly what Stackoverflow does. When I click on a question, I see any votes I have made. When Joe Blogs comes onto the same question he sees his votes.

Move that additional condition into your JOIN:

SELECT * FROM (    
    SELECT row_number() over ( ORDER BY CreationDate DESC) AS rn, *
    FROM (
    SELECT p.id, ownerUserId, p.CreationDate, COUNT( p.id ) over ( partition BY NULL ) AS totalRecordCount, v.voteType
        FROM Posts p
    LEFT OUTER JOIN Votes v on p.id = v.postId AND v.UserID = 1234 /* <-- New bit here */
    ) x
) y
WHERE
rn BETWEEN 1 AND 20