problem with JOIN

[Jaynesh]

Hello

I have an sql query which is working perfectly.

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)
)

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

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)
)

Why is this happening?

[r937]

Why is this happening?

because your joins are all messed up

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

[Jaynesh]

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?

[r937]

Is my JOINs still messed up or is it something else?

yes

this --

JOIN ( dbUsers, dbFriends, dbPosts_share )

is nothing but trouble

did you try the query i gave you?

[Jaynesh]

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?

[Jaynesh]

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.

[r937]

How would I go about cleaning up the entire query?

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

[Jaynesh]

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

[r937]

Is this query healthy?

i can't answer that without knowing your table relationships

but it doesn't look good

[Jaynesh]

Why doesn't it look good?

[r937]

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