Как сформировать json из двух запросов SQL?

Question

[Сергій Мотчаний]

Привет всем. Подскажите кто знает как из двух запросов sql сделать json ответ нужного формата.
База (группы клиентов) №1 clients_groups имеет столбцы id_clients и name.
id_clients тут идут ID клиентов через кому из базы clients.
name тут указан название раздела.
База (Список клиентов) №2 clients имеет данные о пользователях.
Как сделать json. Пример как нужно:

{
        id: '',
        text: 'Отдел №1',
        children: [ // Тут в низу нужно получить список пользователей по ID из отдела №1
            { id: 'ID пользователя', text: 'Имя пользователя' },
            { id: 'ID пользователя', text: 'Имя пользователя' }
        ]
    },
    {
        id: '',
        text: 'Отдел №2',
        children: [ // Тут в низу нужно получить список пользователей по ID из отдела №2
            { id: 'ID пользователя', text: 'Имя пользователя' },
            { id: 'ID пользователя', text: 'Имя пользователя' }
        ]
    }

query("SELECT id, id_clients, name FROM clients_groups", (error, results) => {
                    console.log(results);
                })

При запросе ответ

[
  RowDataPacket { id: 1, id_clients: '2', name: 'Головна' },   
  RowDataPacket { id: 2, id_clients: '2, 3', name: 'Менеджер' }
]

Потом нужно по id_clients получить клиентов из другой таблицы.
Таблица групп пользователей.
https://i2.paste.pics/e472c3be3487a8cc2f6a6cb787ab...
Таблица пользователей.
https://i2.paste.pics/76f533ac74e803d88f1c9f5ca195...

Comments

[alexalexes]

А где, собственно, SQL запросы?

[Сергій Мотчаний]

alexalexes, Спасибо за ответ. Я добавил описание.

[Akina]

Задавая вопрос по SQL:

1. Не добавляйте код и интерпретацию данных от другого языка (в данном случае JS), показывайте только код и данные уровня SQL-сервера.
2. Выложите пример исходных данных в виде отформатированных скриптов CREATE TABLE + INSERT INTO (5-10 записей, не более - либо создайте online fiddle), и покажите требуемый результат запроса для именно этих данных, причём в том виде, в котором этот результат должен быть получен от SQL-сервера. С подробными пояснениями.
3. Укажите, какая СУБД используется, включая точную версию.

Answer 1

[alexalexes]

Вся проблема в том, что таблица clients_groups не нормализована до третьей нормальной формы. У вас атрибут id_clients пытается вобрать несколько значений, что неприменимо при по пытках построить запросы, где будет фигурировать связка с id_client к другим таблицам.
Чтобы данной структурой можно было пользоваться, нужно провести такую нормализацию:
Таблица groups:
id_group - идентификатор группы
name_group - наименование группы
Таблица clients_groups - предназначена для формирования связи многим-ко-многим (связывает множественными связями справочник клиентов со справочником групп):
id - идентификатор связи
id_group - идентификатор группы
id_client - идентификатор клиента
Тогда такая структура будет иметь следующее содержание:
Таблица groups:
id_group, name_group
1, 'Головна'
2, 'Менеджер'
Таблица clients_groups:
id, id_group, id_client
1, 1, 2
2, 2, 2
3, 2, 3
Построение запроса:

select g.id_group, cg.id_client, g.group_name
from groups as g
join clients_groups as cg on g.id_group = cg.id_group

Далее собрать выборку в NodeJS, объединив по группам клиентов и перекодировать результат в JSON.