output being duplicated

[Jaynesh]

Hi, I've setup a simple system. where a user can make a post and it will be visible to his friends. His/her friends can then place a vote on that post.

 

These are my tables.

 

users(id, username)

1 John

2 Mary

3 Jacob

 

friends(my_id, friend_id)

1 2

1 3

3 2

 

posts(post_id, user_id, post)

1 1 John's post

2 3 Jacob's post

3 2 Mary's post

 

 

vote(user_id, post_id, up, down)

1 1 1 0

2 1 0 1

 

This is my query.

"SELECT DISTINCT dbUsers.*, dbPosts.*, dbKarma.*;
FROM posts
LEFT JOIN users
ON posts.username_id = users.id
LEFT JOIN friends
ON (posts.username_id = friends.my_id)
OR
(posts.username_id = friends.friend_id)
LEFT JOIN vote
ON posts.post_id = vote.post_id

The problem is if there is more then 1 vote on a particular post, it will be duplicated. If there are 2 votes on that post it will be duplicated twice.. and 3 votes, duplicated three times.

 

Why is this happening?

[fenway]

Double-post -- actually, quadruple.