Create an array in a qry

[Adamhumbug]

HI All,

I have the following qry

SELECT user_id, user_email, user_firstname, user_lastname, user_mobile, user_role_id, role_name, user_account_level_id, user_level_name, job_eventname
from ssm_user
left join ssm_role
on role_id = user_role_id
left join ssm_user_level
on user_account_level_id = user_level_id
left join ssm_job 
on job_manager_id = user_id
ORDER BY user_id;

Which outputs

user_id       user_email    user_firstname    user_lastname    user_mobile    user_role_id    role_name    user_account_level_id    user_level_name    job_eventname    

1    adam@gmail.com    Adam    Hewitt    07783598021    5    Director    6    System Super User        
2    chelseahockley@mail.com    Chelsea    Hockley    077835482    1    Manager    1    Regular User    A new event    
2    chelseahockley@mail.com    Chelsea    Hockley    077834882    1    Manager    1    Regular User    Chelsea's Event    
8    Email@Address.com    First Name    Last Name    07783598021    2    Chef    1    Regular User        
9    steve@cooking.com    Steve    Jones    07734518273    2    Chef    2    No System Access        
10    ssl@chef.org    Sally    Lawrence    07890736490    2    Chef    2    No System Access        

I would like  "a new event" and "chelsea's event" to be in an array as all of the other information is the same, how would i go about doing this?

[requinix]

GROUP_CONCAT

[Adamhumbug]

5 minutes ago, requinix said:

GROUP_CONCAT

Perfect, thanks you.

This did it

SELECT user_id, user_email, user_firstname, user_lastname, user_mobile, user_role_id, 
role_name, user_account_level_id, user_level_name, 
GROUP_CONCAT(job_eventname) as eventname 
from ssm_user left 
join ssm_role on role_id = user_role_id left 
join ssm_user_level on user_account_level_id = user_level_id left 
join ssm_job on job_manager_id = user_id 
Group by user_id

 

[Barand]

By default the separator is just a comma. IMHO it's more readable if you use

GROUP_CONCAT(job_eventname SEPARATOR ', ') as eventname