SitePoint Sponsor

User Tag List

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

    duplicate results

    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.
    Code:
    "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.

    Why is this happening?

  2. #2
    SitePoint Addict
    Join Date
    Jul 2008
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    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 you're using the dreaded, evil "select star"

    DISTINCT won't do what you want it to, because each of the tables has a unique identifier, so every row returned is distinct
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spiderling View Post
    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.
    Hi.

    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.

    Quote Originally Posted by r937 View Post
    because you're using the dreaded, evil "select star"

    DISTINCT won't do what you want it to, because each of the tables has a unique identifier, so every row returned is distinct
    Hi.

    I tried removing those pretty little stars and adding the specific columns, but still no luck.

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    I tried removing those pretty little stars and adding the specific columns, but still no luck.
    Please post your new query

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

  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)
    i ask again, why are you using LEFT JOINs?

    you're doing the same thing in two of your other threads here on the forums, and you're ignoring my question
    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)
    Quote Originally Posted by r937 View Post
    i ask again, why are you using LEFT JOINs?

    you're doing the same thing in two of your other threads here on the forums, and you're ignoring my question
    I really dont know why
    What should I be using?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Do you want to show the total number of up and down votes?
    Then do a group by and sum the votes:
    Code:
    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

  10. #10
    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
    What should I be using?
    INNER JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Do you want to show the total number of up and down votes?
    Then do a group by and sum the votes:
    Code:
    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
    Hi.

    I want to use the vote data to change the colour of the post depending on what each friend votes for.

    e.g blue for up, red for down.



    Quote Originally Posted by r937 View Post
    INNER JOINs
    Hi. I've read so much about all these different joins but still cant get my head around the differences

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

    I have now managed to get it working without any duplicates.

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

  13. #13
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Hope that helps you in keeping them straight.

  14. #14
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi. Thank you all for your help.

    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)

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

  15. #15
    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 does it compare to my previous one above?
    certainly easier to digest because the UNIONs are gone

    however, i cannot wrap my mind around all those ORs, sorry


    ask yourself two questions --

    1. does your query produce the correct results?

    2. is the performance acceptable?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    certainly easier to digest because the UNIONs are gone

    however, i cannot wrap my mind around all those ORs, sorry


    ask yourself two questions --

    1. does your query produce the correct results?

    2. is the performance acceptable?
    Hi.

    Those ORs are for my friend system.

    instead of having a double record of each relationship I put an OR in the query to treat it from both sides.

    friends(my_id, friend_id)
    1 2

    Instead of
    friends(my_id, friend_id)
    1 2
    2 1

    Is there another approach to this?

    1. does your query produce the correct results?
    Yes

    2. is the performance acceptable?

    The speed is good. how can I test this just to be sure?

  17. #17
    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 there another approach to this?
    yes, store each relationship twice

    disk space is cheap, and the queries will be (1) simpler, and therefore (2) faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, store each relationship twice

    disk space is cheap, and the queries will be (1) simpler, and therefore (2) faster
    Hi. I was told that storing it once was better. I understand your point about disk space being cheaper and will alter my query and tables.


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
  •