Problem with JOIN

[B]Hello

I have an sql query which is working perfectly.[/B]

[I]SELECT DISTINCT dbPosts.post
FROM dbPosts

LEFT 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)
)[/I]

but when I add an extra column, it duplicates each output several times.

[I]SELECT DISTINCT dbPosts.post, dbUsers.username
FROM dbPosts

LEFT 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)
)[/I]

Why is this happening?

because your joins are all messed up :slight_smile:

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

then add another join to the dbPosts_share table

Hey.

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?

yes :slight_smile:

this –

JOIN ( dbUsers, dbFriends, dbPosts_share )

is nothing but trouble

did you try the query i gave you?

Hi,

I tried the query you gave me but it is producing different results. It is also producing duplicates for some reason.

How would I go about cleaning up the entire query?

Also, how would I go about including a column that will not be used in JOIN?

I want to include a specific column to output it in the loop but I don’t need to join it.

sorry, i don’t know what “cleaning up” means

perhaps you could create a test database, containing a small number of related rows, and give us the mysqldump of it

then we can fix the query based on your actual test data

Hello

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
";

i can’t answer that without knowing your table relationships

but it doesn’t look good

Why doesn’t it look good?

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