Jump to content
Adamhumbug

Create an array in a qry

Recommended Posts

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?

Share this post


Link to post
Share on other sites
5 minutes ago, requinix said:

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

 

Share this post


Link to post
Share on other sites

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

GROUP_CONCAT(job_eventname SEPARATOR ', ') as eventname

 

  • Great Answer 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.