you have included the dbPosts_share table in the joins, but forgotten to provide a join condition, consequently you’re getting a cross join where every row of the join is matched with every row of the dbPosts_share table
start with this –
SELECT dbPosts.post
, dbUsers.username
, friend_of.user_id as friend_of
, friend_to.user_id as friend_to
FROM dbPosts
INNER
JOIN dbUsers
ON dbUsers.id = dbPosts.username_id
LEFT OUTER
JOIN dbFriends AS friend_of
ON friend_of.user_id = dbPosts.username_id
LEFT OUTER
JOIN dbFriends AS friend_to
ON friend_to.friend_id = dbPosts.username_id
Sorry I missed out the 2nd part of my query. I havnt forgotten to join dbPosts_share.
SELECT DISTINCT dbPosts.post
FROM dbPosts
JOIN ( dbUsers, dbFriends, dbPosts_share )
ON (
dbPosts.username_id = dbUsers.id
AND (dbPosts.username_id = dbFriends.user_id)
OR
(dbPosts.username_id = dbFriends.friend_id)
)
OR
(dbPosts.username_id = dbUsers.id
AND (dbPosts.post_id = dbPosts_share.post_id)
OR (dbPosts.username_id = dbPosts_share.user_id)
AND
(dbPosts_share.user_id = dbFriends.friend_id)
OR
(dbPosts_share.user_id = dbFriends.user_id))
Is my JOINs still messed up or is it something else?
I have managed to get it working. Thank you for your efforts.
Is this query healthy?
UNION SELECT DISTINCT dbUsers.*, dbPosts.*, dbKarma.*
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 (dbPosts.post_id = dbKarma.karma_post_id)
AND
(dbPosts.username_id = dbKarma.karma_user_id)
WHERE
(dbFriends.friend_id = $user)
OR
(dbFriends.my_id = $user)
UNION SELECT DISTINCT dbUsers.*, dbPosts.*, dbKarma.*
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
";
all those ORs, plus the duplication of the SELECT with UNION, plus the use of DISTINCT
also, the uncertainty of this –
FROM dbPosts
LEFT JOIN dbUsers
ON dbPosts.username_id = dbUsers.id
the fact that you’re using LEFT OUTER JOIN means either that there are posts which have an invalid username_id, or else you weren’t sure about the difference between LEFT OUTER JOIN and INNER JOIN