How to combine data from 2 tables under circumstances?

Question

I have 2 tables. One table contains posts and the other contains votes for the posts. Each member can vote (+ or -) for each post. (Structure example:)

• Posts table: pid, belongs, userp, text.
• Votes table: vid, userv, postid, vote.

Also one table which contains the info for the users.

What I want is: Supposing I am a logged-in member. I want to show all the posts, and at those I've already voted, not let me vote again. (and show me what I have voted + or -)

What I have done til now is very bad as it will do a lot of queries:

SELECT `posts`.*, `users`.`username` 
FROM `posts`,`users` 
WHERE `posts`.belongs=$taken_from_url AND `users`.`usernumber`=`posts`.`userp` 
ORDER BY `posts`.`pid` DESC;

and then:

foreach ($query as $result) {if (logged_in) {select vote from votes....etc} }

So, this means that if I am logged in and it shows 30 posts, then it will do 30 queries to check if at each post I have voted and what I've voted. My question is, can I do it shorter with a JOIN (I guess) and how? (I already tried something, but didn't succeed)

Answer 1

Firstly I'll say that if you're going to have significantly different output for users logged in versus those that aren't, just have two queries rather than trying to create something really complicated.

Secondly, this should do something like what you want:

SELECT p.*, u.username,
  (SELECT SUM(vote) FROM votes WHERE postid = p.pid) total_votes,
  (SELECT vote FROM votes WHERE postid = p.pid AND userv = $logged_in_user_id) my_vote
FROM posts p
JOIN users u ON p.userp = u.usernumber
WHERE p.belongs = $taken_from_url
ORDER BY p.pid DESC

Note: You don't say what the values of the votes table are. I'm assuming it's either +1 (up) or -1 (down) so you can easily find the total votes by adding them up. If you're not doing it this way I suggest you do to make your life easier.

The first correlated subquery can be eliminated by doing a JOIN and GROUP BY but I tend to find the above form much more readable.

So what this does is it joins users to posts, much like you were doing except that it uses JOIN syntax (which again comes down to readability). Then it has two subqueries: the first finds the total votes for that particular post and the second finds out what a particular user's vote was:

• +1: up vote;
• -1: down vote;
• NULL: no vote.