SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem with JOIN

    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?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey.

    Sorry I missed out the 2nd part of my query. I havnt forgotten to join dbPosts_share.

    Code:
    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?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    Is my JOINs still messed up or is it something else?
    yes

    this --
    Code:
    JOIN ( dbUsers, dbFriends, dbPosts_share )
    is nothing but trouble

    did you try the query i gave you?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello

    I have managed to get it working. Thank you for your efforts.

    Is this query healthy?
    Code:
    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
    ";

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    Is this query healthy?
    i can't answer that without knowing your table relationships

    but it doesn't look good
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why doesn't it look good?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    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 --
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •