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.
This a little vague. What data are you trying to retrieve? Users and their individual posts? Users with a count of how many posts they have? Posts and their votes with or without users who posted or users who voted?
Some of your query doesn’t match match the tables provided.
Its a simple script where a user can post something and their friends can view it. This query displays posts to the friend. the friend can also vote on the post. I’m trying to add vote information to the output.
Hi.
I tried removing those pretty little stars and adding the specific columns, but still no luck.
SELECT DISTINCT users.username, users.id, posts.post_id, posts.username_id, vote.up, vote.down
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
All I really want to do is include the vote table in there so I can output data in my loop
Do you want to show the total number of up and down votes?
Then do a group by and sum the votes:
SELECT
users.username
, users.id
, posts.post_id
, posts.username_id
, SUM(vote.up) AS up
, SUM(vote.down) AS down
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
GROUP BY
users.username
, users.id
, posts.post_id
, posts.username_id
I have now managed to get it working without any duplicates.
SELECT $select
FROM dbPosts
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id
LEFT JOIN dbFriends
ON (dbPosts.username_id = dbFriends.my_id)
OR
(dbPosts.username_id = dbFriends.friend_id)
LEFT JOIN dbKarma
ON dbKarma.karma_post_id = dbPosts.post_id
AND
dbKarma.karma_user_id = $user
WHERE
(dbFriends.my_id = $user
AND
dbFriends.status = 1)
OR
(dbFriends.friend_id = $user
AND
dbFriends.status = 1)
GROUP BY
dbUsers.username
, dbUsers.id
, dbPosts.post_id
, dbPosts.username_id
UNION SELECT $select
FROM dbPosts
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id
LEFT JOIN dbPosts_share
ON (dbPosts.post_id = dbPosts_share.share_post_id)
OR (dbPosts.username_id = dbPosts_share.share_user_id)
LEFT JOIN dbFriends
ON (dbPosts_share.share_user_id = dbFriends.friend_id)
OR
(dbPosts_share.share_user_id = dbFriends.my_id)
LEFT JOIN dbKarma
ON dbKarma.karma_post_id = dbPosts.post_id
AND
dbKarma.karma_user_id = $user
WHERE
(dbFriends.friend_id = $user
OR
dbFriends.my_id = $user
)
GROUP BY
dbUsers.username
, dbUsers.id
, dbPosts.post_id
, dbPosts.username_id
UNION SELECT $select
FROM dbPosts
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id
LEFT JOIN dbKarma
ON (dbPosts.post_id = dbKarma.karma_post_id)
AND (dbPosts.username_id = dbKarma.karma_user_id)
WHERE
dbPosts.username_id = $user
LEFT Joins seem to be working. Is it better to keep union select or should it all be merged into one query. if so how would I go about merging?
An INNER JOIN is used when you want matching rows from both tables.
An OUTER JOIN is used when you want all the rows from one table (first table mentioned if you use LEFT OUTER JOIN) and matching rows and NULLs from the other table where no match is found.
Use an INNER JOIN where you want to find all students from your students table and their test marks from the tests table.
Use a LEFT OUTER JOIN where you want all students from your students table and their test marks IF ANY from the tests table.
In the first example only students who have taken a test would show in the results.
In the second example all students would show in the results. If they did not have a value in the tests table then the test scores column would show NULL.
I have cleaned up my query. How does it compare to my previous one above?
(r937, left joins are working fine but I will also be testing out inner joins like you mentioned)
SELECT dbUsers.*, dbPosts.*, dbKarma.*
FROM dbPosts
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id
LEFT JOIN dbKarma
ON (dbKarma.karma_post_id = dbPosts.post_id)
AND
(dbKarma.karma_user_id = $user)
LEFT JOIN dbPosts_share
ON (dbPosts.post_id = dbPosts_share.share_post_id)
OR (dbPosts.username_id = dbPosts_share.share_user_id)
LEFT JOIN dbFriends
ON
dbPosts.username_id = dbFriends.my_id
OR
dbPosts.username_id = dbFriends.friend_id
OR
dbPosts_share.share_user_id = dbFriends.friend_id
OR
dbPosts_share.share_user_id = dbFriends.my_id
WHERE
(dbFriends.my_id = $user)
OR
(dbFriends.friend_id = $user)
AND
(dbFriends.status = 1)
OR
dbPosts.username_id = $user
GROUP BY
dbUsers.username
, dbUsers.id
, dbPosts.post_id
, dbPosts.username_id
