MySQL: Duplicate values when joining from multiple tables

Question

I'm trying to get some news article from my database and also get the related items with it. The database structure looks like this:

news               (id, article        )
newsItemsRelation  (id, newsId,  itemId)
newsAuthorRelation (id, newsId,  userId)
items              (id, itemTitle      )

Then I also have a table for the items and the users with their data.

When I run the query below I get duplicates of the items if I have multiple authors for the same article and I get duplicate authors if I have multiple items for the same article.

SELECT
        article,
        GROUP_CONCAT(name) AS author,
        GROUP_CONCAT(itemTitle) AS item
    FROM news
    LEFT JOIN newsItemsRelation ON
        newsItemsRelation.newsId = news.id
    LEFT JOIN items ON
        items.id = newsItemsRelation.itemId
    LEFT JOIN newsAuthorsRelation ON
        newsAuthorsRelation.newsId = news.id
    LEFT JOIN profiles ON
        profiles.id = newsAuthorsRelation.userId
    GROUP BY news.id

EDIT 1

My result look like this:

Article: Article goes here

Author: Walter White, Dexter Morgan, Walter White, Dexter Morgan

Item: Breaking Bad, Dexter, Breaking Bad, Dexter

EDIT 2

Doing GROUP_CONCAT(DISTINCT name) OR GROUP_CONCAT(DISTINCT itemTitle) doesn't solve my problem since multiple items can have the same itemTitle but different ids. Same goes for authors. That means that my result could look like "Dexter, Dexter", though now that looks like "Dexter, Dexter, Dexter, Dexter"

To answer your question, it would be helpful to see some sample data and desired output. Also, I assume you've tried using the DISTINCT keyword? — sgeddes
– sgeddes, Commented Jan 25, 2013 at 22:43
An article can only have one news.id but one news.id can be connected to multiple items and/or authors. — Oskar Persson
– Oskar Persson, Commented Jan 25, 2013 at 22:47

sgeddes · Accepted Answer · 2013-01-25 23:39:06Z

Your problem is there is no association between your items and your authors. So for each author and each item, you'll get duplicated results.

Try something like this:

    SELECT
        article,
        author,
        item
    FROM news
    LEFT JOIN (
      SELECT 
           News.Id as NewsId,
           GROUP_CONCAT(itemTitle) AS item
       FROM news
       LEFT JOIN newsItemsRelation ON
           newsItemsRelation.newsId = news.id
       LEFT JOIN items ON
           items.id = newsItemsRelation.itemId
       GROUP BY News.Id
      ) items on news.Id = items.NewsId
    LEFT JOIN (
       SELECT 
           News.Id as NewsId,
           GROUP_CONCAT(Name) AS Author
       FROM news
       LEFT JOIN newsAuthorsRelation ON
           newsAuthorsRelation.newsId = news.id
       LEFT JOIN profiles ON
           profiles.id = newsAuthorsRelation.userId
       GROUP BY News.Id
      ) Authors on news.Id = Authors.NewsId

And the SQL Fiddle.

Good luck!

RolandoMySQLDBA · Accepted Answer · 2013-01-25 22:46:42Z

1

Try grouping by article and doing a DISTINCT on the name

SELECT
        article,
        GROUP_CONCAT(DISTINCT name) AS author,
        GROUP_CONCAT(DISTINCT itemTitle) AS item
    FROM news
    LEFT JOIN newsItemsRelation ON
        newsItemsRelation.newsId = news.id
    LEFT JOIN items ON
        items.id = newsItemsRelation.itemId
    LEFT JOIN newsAuthorsRelation ON
        newsAuthorsRelation.newsId = news.id
    LEFT JOIN profiles ON
        profiles.id = newsAuthorsRelation.userId
    GROUP BY article

edited Jan 25, 2013 at 22:46

answered Jan 25, 2013 at 22:40

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

1 Comment

Oskar Persson Over a year ago

Multiple items and authors may have the same name but different ids.

Dave · Accepted Answer · 2013-01-25 23:32:39Z

-1

What I do when I run into this problem is make the query a sub-query and filter from there. Another method is the column sub-query method (hate this one as it is very expensive).

answered Jan 25, 2013 at 23:32

Dave

1,24413 silver badges24 bronze badges

Collectives™ on Stack Overflow

MySQL: Duplicate values when joining from multiple tables

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related