Getting duplicate posts from a joined mysql query

[rpkamp]

@r937

so if a group post can have only one author, but that author can have many updates, that might explain why each post is showing multiple times in your query results

@gsshanker10 did you look at that part too?

[r937]

@gsshanker10

still getting duplicate reults pl take a look at the following output from the latest execution of the query.

i see 5 rows but i don’t see any duplication – please point it out to me, which of these 5 rows are duplicates?

[gsshanker10]

@rpkamp no sir. but i can’t understand how that brings the duplicate rows in Updates table. if thats the case isn’t there a way to bring distinct results from the joined query especially from the updates table like the group_posts table.

[gsshanker10]

@r937 the following tables are producing the same repeated values. up1,update_body,time,tit2,account_name, ty2,author, dat2.

[gsshanker10]

@rpkamp to be honest i don’t know how to check that.sorry.

[DaveMaxwell]

@gsshanker10

the following tables are producing the same repeated values.

And they are going to because of how your update table works. You are matching by author, not by any sort of tie-in to the post (which I don’t see any other way to tie those two tables together…). A join will show all matches between the two tables.

So if you have three rows in group_posts with the author ‘shan2batman’, and ten rows in updates with the author ‘shan2batman’, you’re going to end up with 30 rows of data

• Ten rows which will have the content of the first row of group_posts, with the content from each row in updates.
• Ten rows which will have the content of the second row of group_posts, with the content from each row in updates.
• Ten rows which will have the content of the third row of group_posts, with the content from each row in updates.

[gsshanker10]

@DaveMaxwell i changed the query and still get the same results.Pl check this.

select distinct gp.gp_id as gp1,gp.pid,gp.author_gp,gp.gname,gp.type as ty1,gp.title as tit1,gp.data as dat1,gp.pdate,gp.group_id,gp.author_id,
   
            up.update_id as up1,up.update_body,up.time,up.title as tit2,up.account_name,up.author,up.type as ty2,up.data as dat2 
            from group_posts as gp
          
           inner join updates as up on gp.author_id=up.user_id_u
            where 
            gp.group_id=25 and gp.author_id=127 and up.update_id not in(386)
           order by time,pdate desc limit 0,5

here are the results of that query.

[DaveMaxwell]

The author_id makes sense - if you have an author repeating, the author id should be the same.
Same for the group id - what group are those posts for?

Take the ORDER BY out and order it by gp_id and update_id and you’ll see what I’m talking about with how the joins work…