Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14
  1. #1
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    having problems with repeated values in mysql joined query

    hi,
    i need help in creating a working joined query between many tables for a project.The problem is i'm getting repeated values when i run the query .If you need sql dump i'm willing to do so

    and here it is
    Table 1:-(user)
    The user.user_id table ia a primary one and occurs many times once in different tables and user.uname is kind of primary and occurs many times in different tables(it is not a defined primaary key like user_id)
    1)updates.user_id_u is secondary to user.user_id and can occur many times
    2)updates.account_name and updates.author is secondary to user.uname and can occur many times
    3)group_posts.author_id is secondary to user.user_id and can occur many times
    4)group_posts.author_gp is secondary to user.uname and can occur many times based on different users.
    5)user.user_id is primary to voted_u.user_id_v(table which contains like and dislike columns for updates) and can occur many times
    6)voted_g_c.user_id_reply_g is secondary to user.user_id and can occur many times as the user might click on different links in the page.
    7)voted_c.user_id_reply is secondary to user.user_id and can occur many times as the user might click on many links in the page for updates table comments.

    Table 2:-(group_posts)
    group_posts.gp_id is primary while group_posts.pid is also kind of primary for group post comments(it means a comment to gp_id and can occur many times)
    1)group_posts.gname is secondary to groups.name and can occur many times as it is the name of the group.
    2)voted_g_u.vote_4_gid(table for group_posts users like or dislike button clicks) is secondary to group_posts.g_id and can occur many times as users might click on the same like or dislike button
    3)group_posts.group_id is secondary to groups.g_id and can occur many times as many users might post in the same group.
    4)group_posts.author_id is secondary to user.user_id and can occur many times.

    Table 3:-(groups)
    groups.g_id is primary and occurs only once, so is groups.name.
    1)groups.creator is secondary to user.uname and can occur many times as the user might create multiple groups.

    Table 4:-(comment_update)
    comment_update.comment_id is primary and occurs only once.
    1)comment_update.os_id(original status id) is secondary to update.update_id and can occur many times as users might write comments to the same update.
    2)comment_update.author_c is relational to u.uname and can occur many times
    3)comment_update.user_id_c is relational to user.user_id and can occur many times

    Table 4:- (gmembers or group members what i want to do is i want to fetch users of the same group and get their group_updates)
    1)gmembers.gname is actually group name and is secondary to groups.name and can occur many times
    2)gmembers.mname is the name of the user or is relational to user.uname and can occur many times
    3)gmembers.group_id is secondary to groups.g_id and can occur many times
    4)gmembers.user_id_group is secondary to user.user_id and can occur many times as the might be a member of many groups

    Table 5:-(voted_u and voted_c)
    1)voted_u.vote_4_update_id is secondary to updates.update_id and can occur many times
    2)voted_u.user_id_v is secondary to user.user_id and can occur many times for the same voted_u.vote_4_update_id
    3)voted_c.vote_4_reply_id is secondary to comment_update.comment_id and can occur many times
    4)voted_c.user_id_reply is secondary to user.user_id and can occur many times
    Table 6:-(voted_g_u & voted_g_c)
    the primary key for both voted_g_u &voted_g_c like voted_u and voted_c doesnt occur to have secondary values
    1)voted_g_u.vote_4_gid is secondary to group_posts.gp_id and can occur many times.

    Query:-
    PHP Code:
    select DISTINCTROW up.*,g.* ,cu.*,gm.*,gp.*,v_g.*,v_g_c.*,v_u.*,v_c.* ,u.uname,u.avatar,u.user_id from group_posts as 
    left outer join user 
    as 
    on u
    .user_id=g.author_id 
    left outer join updates 
    as up 
    on up
    .user_id_u=u.user_id 
    left outer join groups 
    as gp 
    on g
    .group_id=gp.g_id 
    left outer join gmembers 
    as gm 
    on gm
    .user_id_group=g.author_id 
    left outer join comment_update 
    as cu 
    on cu
    .os_id=up.update_id 
    left outer join voted_g_u 
    as v_g 
    on v_g
    .vote_4_gid=g.gp_id 
    left outer join voted_g_c 
    as v_g_c 
    on v_g_c
    .vote_4_reply_id=g.pid 
    left outer join voted_u 
    as v_u 
    on v_u
    .vote_4_update_id=up.update_id 
    left outer join voted_c 
    as v_c 
    on v_c
    .vote_4_reply_id=cu.comment_id 
    where g
    .group_id=25 and u.user_id=127 GROUP by up.update_id,g.gp_id order by u.user_id desc 

  2. #2
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,544
    Thanks
    6
    Thanked 155 Times in 150 Posts
    Post an SQL dump of your DB and then describe the real problem you are trying solve instead of your attempt at solving it.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  3. #3
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here is the sql dump sql dump, MySQL - rextester
    the problem is is i get repeated duplicate values when i run the above query

  4. #4
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,544
    Thanks
    6
    Thanked 155 Times in 150 Posts
    Ok, now lets forget your query, tell us what you want to know about the data.

    Edit: I just loaded your SQL. First thing I noticed is you do not have have any Foreign Keys and proper Indexes. Because of that you already have bad data. It also makes it difficult to see what is tied to what. These things need to be fixed first before you do anything else.

    1. Fix the duplicate data, specifically usernames AND emails (You have both)
    2. Create the proper indexes
    3. Create the Foreign Keys
    4. Re-post your DB Dump
    Last edited by benanamen; Oct 4th, 2018 at 02:58 AM.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  5. #5
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here is the updated dump updated_query, MySQL - rextester

  6. #6
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    pl use this instead of the previous dump proper_update, MySQL - rextester

  7. #7
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,544
    Thanks
    6
    Thanked 155 Times in 150 Posts
    There are still a number of tables with no keys. You also have not specified what you want to know about the data.

    You are also using MD5 for password hashing. That is very bad.
    PM your SQL. You are publicly posting sensitive user data. Delete your dumps ASAP.

    Edit:
    I was able to hack numerous passwords simply by pasting them into an online password cracker. Your application code is EXTREMELY Insecure. At this point you should consider your entire application and all your users compromised. IMMEDIATELY take your application offline and implement proper password hashing. When you have it correct and back online, force all users to choose a NEW password using the proper hashing algorithm.
    Last edited by benanamen; Oct 4th, 2018 at 06:03 PM.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  8. #8
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @benanamen did you come up with a query that eliminates duplicate values sir??

  9. #9
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,544
    Thanks
    6
    Thanked 155 Times in 150 Posts
    I have been busy. Have not had time to look into it.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  10. #10
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ok pl look in to it after you finish your work.

  11. #11
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    @benanamen can you pl find a solution to the joined query pl.

  12. #12
    Senior Coder benanamen's Avatar
    Join Date
    Oct 2015
    Posts
    1,544
    Thanks
    6
    Thanked 155 Times in 150 Posts
    Please tell me your not just sitting around waiting for me to do it. Have you made any further attempts to solve it yourself? I will look at it as soon as I am able. If it is really important, you can hire me and get top priority just like the other people that hire me who are the ones keeping you waiting for free work.
    To save time, lets just assume I am almost never wrong.

    The XY Problem
    The XY problem is asking about your attempted solution (X) rather than your actual problem (Y). This leads to enormous amounts of wasted time and energy, both on the part of people asking for help, and on the part of those providing help.

    Make A Donation https://www.paypal.me/KevinRubio

  13. #13
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i'll post my latest query ASAP, i thought you might have got free don't get me the wrong way
    Last edited by shan2batman; Oct 13th, 2018 at 11:02 AM.

  14. #14
    New to the CF scene
    Join Date
    Oct 2018
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here is my latest working query:

    Code:
    select DISTINCTROW up.update_id,g.gp_id,up.update_body,up.time,up.title,up.user_id_u,up.account_name,up.data,g.gname,g.pid,g.author_gp,g.type,g.title,g.pdate,g.group_id,g.author_id,cu.comment_id,cu.comment_body,cu.time_c,cu.os_id,cu.author_c,cu.user_id_c,gm.gm_id,gm.gname,gm.mname,gm.approved,gm.group_id,gm.user_id_group,gp.*,v_g.*,v_g_c.*,v_u.*,v_c.* ,u.uname,u.avatar,u.user_id from group_posts as g 
    left outer join user as u 
    on u.user_id=g.author_id 
    left outer join updates as up 
    on up.user_id_u=u.user_id 
    left outer join groups as gp 
    on g.group_id=gp.g_id 
    left outer join gmembers as gm 
    on gm.user_id_group=g.author_id 
    left outer join comment_update as cu 
    on cu.os_id=up.update_id 
    left outer join voted_g_u as v_g 
    on v_g.vote_4_gid=g.gp_id 
    left outer join voted_g_c as v_g_c 
    on v_g_c.vote_4_reply_id=g.pid 
    left outer join voted_u as v_u 
    on v_u.vote_4_update_id=up.update_id 
    left outer join voted_c as v_c 
    on v_c.vote_4_reply_id=cu.comment_id 
    where gp.g_id=25 and u.user_id=127 GROUP by u.user_id ,gp.g_id order by up.time,g.pdate desc
    the problem now is i get only one value from all the tables


 

Tags for this Thread

Posting Permissions

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