MySQL - JOIN multiple rows to single row multiple times

Question

I am trying to join multiple rows of information for single row, but it seems to multiply every time there is more rows in one of the joins.

My tables structure is as follows:

news
id  |  title  |  public
------------------------
1   |  Test   |  0


news_groups_map
id  |  news_id  |  members_group_id
------------------------------------
1   |  1        |  5
2   |  2        |  6


members_groups_map
id  |  member_id  |  group_id
------------------------------
1   |  750        |  5
2   |  750        |  6

The query I've got so far is:

SELECT 
n.title, 
n.public, 

CAST(GROUP_CONCAT(ngm.members_group_id) AS CHAR(1000)) AS news_groups,
CAST(GROUP_CONCAT(member_groups.group_id) AS CHAR(1000)) AS user_groups

FROM news n

LEFT JOIN news_groups_map ngm ON n.id = ngm.news_id

JOIN (
    SELECT group_id
    FROM members_groups_map
    WHERE member_id = 750
) member_groups

WHERE n.public = 0
GROUP BY n.id

However, the result is as follows:

title  |  public  |  news_groups  |  user_groups
-------------------------------------------------
Test   |  0       |  5,6,5,6      |  6,6,5,5

As you can see, the news_group and user_groups are duplicating, so if a news article is in 3 groups, the user_groups will be multiplied as well and show something like 5,6,6,6,5,5.

How can I group those groups, so that they are only displayed once?

The ultimate goal here is to compare news_groups and user_groups. So if at least one group matches (meaning user has enough permissions), then there should be a boolean with true returned, and false otherwise. I don't know how to do that either, however, I thought I should sort out the grouping first, as once the number of groups gets bigger there is going to be unnecessary lots of same data selected.

Thanks!

Gordon Linoff · Accepted Answer · 2016-09-19 11:52:44Z

1

The simplest method is to use distinct:

SELECT n.title, n.public, 
       GROUP_CONCAT(DISTINCT ngm.members_group_id) AS news_groups,     
       GROUP_CONCAT(DISTINCT mg.group_id) AS user_groups
FROM news n LEFT JOIN
     news_groups_map ngm
     ON n.id = ngm.news_id CROSS JOIN
     (SELECT group_id
      FROM members_groups_map
      WHERE member_id = 750
     ) mg
WHERE n.public = 0
GROUP BY n.id;

This query doesn't actually make sense. First, the subquery is not needed:

SELECT n.title, n.public, 
       GROUP_CONCAT(DISTINCT ngm.members_group_id) AS news_groups,     
       GROUP_CONCAT(DISTINCTD mg.group_id) AS user_groups
FROM news n LEFT JOIN
     news_groups_map ngm
     ON n.id = ngm.news_id CROSS JOIN
     members_groups_map mg
     ON member_id = 750
WHERE n.public = 0
GROUP BY n.id;

Second, the CROSS JOIN (or equivalently, JOIN without an ON clause) doesn't make sense. Normally, I would expect a join condition to one of the other tables.

edited Sep 19, 2016 at 11:52

answered Sep 19, 2016 at 11:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Giedrius Over a year ago

Thanks, DISTINCT didn't even come to my head for some reason. You make a good point, I could just get the user_groups ready before the query instead of the member_id. In that case, how could I use that to cross-check with news_groups? For single value I could use IN, but how can I check multiple values with functionality of or (so either value match)?

StanislavL · Accepted Answer · 2016-09-19 11:02:43Z

1

Use DISTINCT in the GROUP_CONCAT

...
CAST(GROUP_CONCAT(DISTINCT ngm.members_group_id) AS CHAR(1000)) AS news_groups,
CAST(GROUP_CONCAT(DISTINCT member_groups.group_id) AS CHAR(1000)) AS user_groups
...

answered Sep 19, 2016 at 11:02

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Collectives™ on Stack Overflow

MySQL - JOIN multiple rows to single row multiple times

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related