Giedrius Giedrius - 2 months ago 10
SQL Question

MySQL - JOIN multiple rows to single row multiple times

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!

Answer

The simplest method is to use distinct:

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