CODEWITHSUNDEEP
mysqlgroup-byduplicatesdistinctinner-join
hasentopf
hasentopf

Reputation: 757

MySQL query with two INNER JOIN returns duplicate entries in the result

I have the following data structure: Articles have m:n Users

There are three tables: articles, users and articles_users (linking table)

Now I need this query: Give me all Users who have recently written an Article.

Unfortunately, this query returns duplicate results:

SELECT  DISTINCT users.id, 
        users.username, 
        articles.published, 
        articles_users.user_id
FROM    users 
        INNER JOIN articles_users 
            ON users.id = articles_users.user_id
        INNER JOIN articles     
            ON articles.id = articles_users.article_id
ORDER BY articles.published DESC
LIMIT 25;

Result:

id      username        published   user_id
113     silva_mihat     2012-10-30  112
228     paula_tille     2012-10-27  258
228     paula_tille     2012-10-26  258
631     andrea_gurkow   2012-10-24  631
275     hubert_mayer    2012-10-24  275
198     annette_mulger  2012-10-22  198
255     uta_zuffter     2012-10-22  235
and so on ...

Does anyone have an idea why DISTINCT isn't working here?

Upvotes: 2

Views: 4333

Answers (2)

user645280
user645280

Reputation:

This should group by author instead of of by article. 

select
  users.id,
  users.username,
  maxPublished
from users
inner join (
  select
    max(articles.published) as maxPublished,
    articles_users.user_id as userID
  from articles
  join articles_users on articles_users.article_id = articles.id
  group by articles_users.user_id
) as p on users.id = userID
order by maxPublished desc
limit 25
;

Upvotes: 1

John Woo
John Woo

Reputation: 263733

because DISTINCT applies to the whole row (not just the users.id itself). as you can see all the rows returned are not unique. Try something like this, the idea behind the subquery is it gets the recent published date for each article_id

SELECT  users.id, 
        users.username, 
        articles.published
FROM    users 
        INNER JOIN articles_users 
            ON users.id = articles_users.user_id
        INNER JOIN articles 
            ON articles.id = articles_users.article_id
        INNER JOIN 
        (
            SELECT id, MAX(published) maxDate
            FROM articles
            GROUP BY id
        ) c ON  articles.id = c.ID AND 
                articles.published = c.maxDATE
-- ORDER BY articles.published DESC
-- LIMIT 25

Upvotes: 1

Related Questions