MySQL Forums
Forum List  »  PHP

need help in creating a joined query
Posted by: gowri shankar
Date: October 02, 2018 11:51AM

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:-
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 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 g.group_id=25 and u.user_id=127 GROUP by up.update_id,g.gp_id order by u.user_id desc

Options: ReplyQuote


Subject
Written By
Posted
need help in creating a joined query
October 02, 2018 11:51AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.