Voting Script, the ability to simplify database queries

Question

I have a voting script that stores post_id and user_id in a table to determine if a particular user voted for a message and banned them in the future.

To do this, I perform the following 3 queries.

SELECT user_id, post_id from votes_table where postid=? AND user_id=?

If this does not return rows, then:

UPDATE post_table set votecount = votecount-1 where post_id = ?

Then

SELECT votecount from post where post_id=?

To display a new voting score on a web page

Is there a better way to do this? 3 requests seriously slow down the user voting process

Edit

• In the vote table, vote_id is the primary key
• In the message table, post_id is the primary key.
• Any other suggestions to speed things up?

Answer 1

You can combine the first two queries:

UPDATE  post
SET     votecount = votecount - 1
WHERE   post_id = ?
        AND post_id NOT IN
        (
        SELECT  post_id
        FROM    votes_table
        WHERE   user_id = ?
        )

.

, PRIMARY KEY votes_table (user_id, post_id) post (post_id).

, .

Answer 2

Quassnoi post_id, - NOT IN.

UPDATE 
    dbo.Post
SET 
    VoteCount = VoteCount + 1 -- Must be +1 right? Not minus 1?
FROM
    dbo.Post p
WHERE
    NOT EXISTS
        (
        SELECT
            v.PostID
        FROM
            dbo.Votes_Table v
        WHERE
            v.PostID = p.PostID
        AND v.User_ID = ?
        )
WHERE
    p.PostID = ?

IF(@@ROWCOUNT = 1)
    PRINT 'No previous vote, votes incremented'
ELSE
    PRINT 'User has already voted, votes not incremented'

vote_count .