Group_Concat function does not show all data in SQL search

Question

I have a problem that I can't solve with a search in a database. I have a sentence that works perfectly and has been working for a while, which is the following.

return await conn
        .query(
          `SELECT ig.id_group AS id_group, ig.nick, ig.id_project,GROUP_CONCAT(isg.id_subgroup) AS id_subgroups,irg.permissions AS permissions, irg.shared AS shared
              FROM r_user_group irg 
              LEFT JOIN i_groups ig ON  irg.id_group = ig.id_group
              LEFT JOIN i_subgroups isg ON ig.id_group = isg.id_group
              WHERE irg.id_user = ?
              GROUP BY ig.id_group;`,
          [userId]

Now we are passing the queries to search by object much cleaner. but this search or any that contains Group_Concat only returns the first data and ignores the others. Any idea where I am failing?

 let objData : SqlSentence = {
        busqueda: {
          campos: ['permissions','shared'],
          tabla: 'r_user_group',
          condicion: ['id_user']
        },
        join:[
          {
            tabla: 'i_groups',
            tablaUnion: 'r_user_group',
            union: 'id_group',
            campos: ['id_group','nick','id_project'],
            group:['id_group']
          },
          {
            tabla:'i_subgroups',
            aliasTabla: 'isg',
            tablaUnion:'i_groups',
            union:'id_group',
            funciones:['GROUP_CONCAT(isg.id_subgroup) AS id_subgroups']
          }
        ]
      }

Thank so much

slaakso · Accepted Answer · 2023-02-28 15:53:33Z

0

Use all non-aggregate columns in the GROUP BY. In your case:

GROUP BY ig.id_group, ig.nick, ig.id_project

answered Feb 28, 2023 at 15:53

slaakso

9,1452 gold badges19 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group_Concat function does not show all data in SQL search

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related