CODEWITHSUNDEEP
mysqlsqldatabaseleft-join
Oskar Persson
Oskar Persson

Reputation: 6753

MySQL: Duplicate values when joining from multiple tables

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"

Upvotes: 1

Views: 3982

Answers (3)

sgeddes
sgeddes

Reputation: 62831

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!

Upvotes: 2

Dave
Dave

Reputation: 1234

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).

Upvotes: -1

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

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

Upvotes: 1

Related Questions