MySQL Select use IN and GROUP BY

Question

I have two tables messages and users I want to find out which users received the messages however the query is only returning one message.

My Schemas are as follow

Messages

msg_id | msg_content | recipients |
-----------------------------------
1      | Hello world | 1,2,3,4,5
2      | Test        | 1,3,5
3      | Welcome     | 1,2,4

Users

uid    | fname   | lname  |
---------------------------
1      | John    |Doe     |
2      | Jane    |Doe     |
3      | Mark    |Someone |
4      | Mary    |lady    |
5      | Anthony |Doe     |

So I would love to see my results simply as

msg_id | msg_content | recipients |
-----------------------------------
1      | Hello world | John,Jane,Mark,Mary,Anthony
2      | Test        | John,Mark,Anthony
3      | Welcome     | John,Jane,Mary

So I am doing my query as so

SELECT msg_id,msg_content,fname AS recepients FROM messages a
LEFT JOIN users ON uid IN(a.recipients)

When I run that query I only get one recipient. Please advice. Thanks.

Thilina Dharmasena · Accepted Answer · 2015-09-01T06:43:20.237

I think you have to use a alternative way for create tables

Messages

msg_id | msg_content | 
----------------------
1      | Hello world |
2      | Test        |
3      | Welcome     |

Users

 uid    | fname   | lname  |
---------------------------
1      | John    |Doe     |
2      | Jane    |Doe     |
3      | Mark    |Someone |
4      | Mary    |lady    |
5      | Anthony |Doe     |

users_has_messages

uhm_id | uid | msg_id  |
---------------------------
1      | 1   |  1      |
2      | 2   |  1      |
3      | 3   |  1      |
4      | 2   |  2      |
5      | 1   |  3      |

Then you can use your code

score 1 · Answer 2 · answered Sep 01 '15 at 04:07

1

Okay, so this schema isn't the best (using comma separated lists of IDs is not a great idea, and the performance of any joins will get pretty bad pretty quick). Best bet is to have a third table mapping uid's to msg_id's as mentioned by @Thilina.

That said, this query will do probably what you're after:

SELECT msg_id,msg_content,GROUP_CONCAT(fname) AS recepients FROM messages a
LEFT JOIN users ON FIND_IN_SET(uid, a.recipients)
GROUP BY msg_id

answered Sep 01 '15 at 04:07

Jon Marnock

3,155
1
20
15

This query does not run there is an error "Unknown column 'a.recipients' in 'on clause'" – sammyukavi Sep 02 '15 at 18:27
@Ukavi - it's your query I used, so if that's not the column name, you should fix it up to match whatever the equivalent column name is. – Jon Marnock Sep 03 '15 at 04:37

score 0 · Answer 3 · answered Sep 01 '15 at 04:23

I tried this in Oracle 12c and it is working fine.

So basically what I did is - Separate the userid from recipient field and used this a columns. - Join with USERS table to get user fnames - Used LISTAGG function to aggregate it back.

For MySql we need to find the corresponding functions to Separate the IDs between commas, Convert it to rows and Aggregate. But the inherent logic would be same.

    with users (user_id,fname) as (
    select  1   ,'John' from dual union
    select  2   ,'Jane' from dual union
    select  3   ,'Mark' from dual union
    select  4   ,'Mary' from dual union
    select  5   ,'Anthony' from dual
    ),
    messages(msg_id, msg_content,recipients) as(
    select 1,'Hello world','1,2,3,4,5' from dual union
    select 2 ,    'Test' ,'1,3,5' from dual union
    select 3,' Welcome','1,2,4' from dual
    ),
    flat as(
    select msg_id,msg_content, 
     REGEXP_SUBSTR (recipients, '[^,]+', 1, COLUMN_VALUE) as user_id
     from messages,
                 TABLE(
                     CAST(
                       MULTISET(
                         SELECT LEVEL
                         FROM   DUAL
                         CONNECT BY LEVEL <= REGEXP_COUNT(recipients ,',' ) + 1
                       ) AS SYS.ODCINUMBERLIST
                     )
                   )
      ),
      unames as
     ( select f.msg_id,f.msg_content,u.fname from flat f inner join users u
      on f.user_id = u.user_id
      order by f.msg_id
      )
          SELECT msg_id,msg_content,LISTAGG(fname, ',') WITHIN GROUP (ORDER BY fname) as recipients
                 from unames        
                 group by msg_id,msg_content

MySQL Select use IN and GROUP BY

3 Answers3