SELECT LIST returns only one value and not a list

Question

Hello,

I have two tables, one representing the groups (Certification.certification_adm_email - columns start with "fall"), and the other users (admin.dbuser - columns start with "user"). Among them there is an nxn table doing the intermediation (Certification.caem_dbuser - columns start with "cadb"). I need to make a query that returns me to a group, all users registered in it. However, my search is returning only one result to this field I called members_desc. Could anyone help, please?

For now, my code is like this:

                 SELECT
                    caem_seq,
                    caem_code,
                    caem_desc,
                    cadb_user_seq,
                    cadb_caem_seq,
                    (SELECT LIST(user_name || '-' || user_elogin || '-' || cmpn_desc || '-' || user_email, '<br>' ORDER BY user_name)
                       FROM admin.dbuser
                       JOIN admin.company ON cmpn_seq = user_cmpn_seq
                      WHERE user_seq = cadb_user_seq
                    ) AS members_desc
          FROM  certification.certification_adm_email
          LEFT JOIN certification.caem_dbuser ON cadb_caem_seq = caem_seq

Answer 1

I managed to, thank you!

Worked with the following code:

            SELECT
                        caem_seq,
                        caem_code,
                        caem_desc,
                        (SELECT LIST('-' || user_name || '-' || user_elogin || '-' || cmpn_desc || '-' || user_email, '<br>' ORDER BY user_name)
                           FROM certification.caem_dbuser
                           JOIN admin.dbuser ON cadb_user_seq = user_seq
                           JOIN admin.company ON cmpn_seq = user_cmpn_seq  
                          WHERE caem_seq = cadb_caem_seq
                        ) AS members_desc
              FROM  certification.certification_adm_email

Answer 2

When using the members_desc as a select you force the instruction to bring only one result You can create a temp with this select and do left join in the from

SELECT
     caem_seq,caem_code,caem_desc,cadb_user_seq,cadb_caem_seq,

FROM  
     certification.certification_adm_email
     LEFT JOIN certification.caem_dbuser ON cadb_caem_seq = caem_seq
     left join (select user_name + '-' + user_elogin + '-' + cmpn_desc + '-' + user_email 'members_desc ',  '<br>'
                       FROM admin.dbuser
                       JOIN admin.company ON cmpn_seq = user_cmpn_seq
                      
                    ) AS x on user_seq = cadb_user_seq  

Something like that ...