no, i gave up because you never replied with the one-to-many analysis
i didn’t create any foreign keys over there. yet i explained all the relations every table has to the area.
Not like he asked. You explained the fields it’s joined on, but you didn’t explain which ones were the ones and which ones were the many.
He’s looking for something like (and I’m making this up using your table names). You need to do this for each join you have.
- Each row in user can have more than one row in group_posts
- Each row in group_posts can have only one row in user
in the user table the user_id has one rows in group_posts table where group_posts.author_id and user.uname is relationable to user.user_id and user.uname and group_posts has no other relational references with user table as,no other column is relational to any other column as the rest of the columns in user is password, email, etc.
In the updates table user table has two columns that is referenced by updates.user_id_u to user.user_id and the updates.account_name and updates.author is referenced to user.uname and no other table from updates is connected to user table.
The group_posts as mentioned earlier is referenced by user table user.user_id to group_posts.author_id and group_posts.author_gp is referenced to user.uname other than that group_posts.gname is relationable to groups.name and groups.g_id is relationable to group_posts.group_id and the rest of the columns is not relational to any other table.
The groups table has two tables that is relationable to group_posts and to only one other table that is user. here the groups.g_id is referenced to group_posts.group_id and groups.name is referenced to group_posts.gname and the column that references user table is groups.creator to user.uname.
@DaveMaxwell is this correct way to explain the relationships.
Nope. You STILL didn’t answer the question he asked. You are providing which fields match up between the tables. That’s all well and good. But that doesn’t tell which ones are primary (meaning only occur once) and which are secondary (which occur many times)
Let’s look at the sample tables below. If you were explaining the relationships, what you’ve essentially said is
- id on Table1 relates to table1id onTable3
- id on Table2 relates to table2id onTable3
Table1
ID First Name Last Name
1 John Smith
2 Jane Doe
3 Ron Brown
Table2
ID ProductName Price
1 Train $10
2 Car $5
3 Plane $20
Table3
Table1ID Table2ID
1 1
1 3
2 1
2 2
2 3
3 2
3 3
What @r937 has asked (repeatedly) is for
- Each row in Table1 can have multiple rows in Table3
- Each row in Table3 can have only one row in Table1
- Each row in Table2 can have multiple rows in Table3
- Each row in Table3 can have only one row in Table2
Now, to be REALLY thorough, what you SHOULD provide is a combination of the two
- Each row in Table 1 can have multiple rows in Table3 matched up by Table1.id to Table3.table1id
- Each row in Table 3 can have only one row in Table1 matched up by Table1.id to Table3.table1id
- Each row in Table 2 can have multiple rows in Table3 matched up by Table2.id to Table3.table2id
- Each row in Table 3 can have only one row in Table2 matched up by Table2.id to Table3.table2id
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.
@DaveMaxwell though i’m not sure let me know where i’m going wrong this time.
@r937 is this good enough
i repeat my advice in post #16
@r937 the idea of my tables are simple user_id and uname are one and the rest of the other references are many. Likewise, groups.g_id is one the rest is many
if you can’t get the idea then i have to give the sql dump which i’m willing to give for reference
What data do you REALLY need from this query? I sincerely doubt you need ALL of the fields in the group_posts AND comment_update AND gmembers AND groups AND updates plus the username, avatar and user_id from user
Start with ONLY the fields you really need, then go from there.
select DISTINCTROW g.* ,cu.*,gm.*,gp.*,up.*, u.uname,u.avatar,u.user_id
if @r937 if you are in the mood to entertain me here is the sqlfiddle for my db i added some indexes and foreign keys http://rextester.com/CNLF30867
sorry, not until you are in the mood to identify the one-to-many relationships
I know this sounds a bit “anti-tech” but it might help if you use paper and pencil to visualize what you need and what the relationships are.
For example, list (only) the names of the tables you need to use and beneath them list (only) the names of the fields you need to use. Here’s where it can get messy. Draw arrows from the fields to other fields they relate to. One arrow for a “to one” relationship, two arrows for a “to many” relationship.
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.