How work with SQL and multiple join or merge them

I need to retrieve events from table, with skills requirements and user engagements and skills.

For a project, I have :

an users table
a skills table with inheritance from others skills
an users_skills table with skills of users
an events table
an events_skills table with the quantity of a required skills for the events
an events_users table with users engagements on events

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` char(32) NOT NULL
);

CREATE TABLE `skills` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` char(64) NOT NULL,
  `parent_id` int(11) unsigned DEFAULT NULL,
  FOREIGN KEY (`parent_id`) REFERENCES `skills` (`id`)
);

CREATE TABLE `users_skills` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int(11) unsigned NOT NULL,
  `skill_id` int(11) unsigned NOT NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`)
);

CREATE TABLE `events` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` char(64) NOT NULL
);

CREATE TABLE `events_skills` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `event_id` int(11) unsigned NOT NULL,
  `skill_id` int(11) unsigned NOT NULL,
  `quantity` smallint unsigned NOT NULL,
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`),
  FOREIGN KEY (`skill_id`) REFERENCES `skills` (`id`)
);

CREATE TABLE `events_users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `event_id` int(11) unsigned NOT NULL,
  `user_id` int(11) unsigned NOT NULL,
  FOREIGN KEY (`event_id`) REFERENCES `events` (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

I want to retrieve all events, with skills requirments and users participation.

My first idea was to use LEFT JOIN :

SELECT  e.id, e.name, es.skill_id, s.name skill_name, es.quantity,
        eu.user_id, u.name user_name, us.skill_id user_skill_id,
        uss.name user_skill_name
    FROM  events e
    LEFT JOIN  events_skills es  ON es.event_id = e.id
    LEFT JOIN  skills s  ON s.id = es.skill_id
    LEFT JOIN  events_users eu  ON eu.event_id = e.id
    LEFT JOIN  users u  ON u.id = eu.user_id
    LEFT JOIN  users_skills us  ON us.user_id = u.id
    LEFT JOIN  skills uss  ON uss.id = us.skill_id;

id  name    skill_id    skill_name  quantity    user_id user_name   user_skill_id   user_skill_name
1   eve1    2           ski2        3           1       use1        2               ski2
1   eve1    3           ski2-2      1           1       use1        2               ski2
1   eve1    2           ski2        3           2       use2        NULL            NULL
1   eve1    3           ski2-2      1           2       use2        NULL            NULL
2   eve2    NULL        NULL        NULL        NULL    NULL        NULL            NULL

2 differents events, second without users and skills. First, with 2 users, first with one skills, seconds without skills, and finally, two skills requirements.

It work "fine", but it's verry ugly, and I think it can be very slow if an event or an user has too many skills. Also, each event has a lot of lines, and it's difficult to sort them (if I want to display only 20 events...).

To improve, I think I can load all skills separately and remove the two LEFT JOIN for table skills.

But, can I merge all skills and quantity in one column, and users in others ? Did you have betters solutions/improvements ?

id  name    skills  users
1   eve1    2,3;3,1 1,2;2,NULL
2   eve2    NULL    NULL

Thanks for your help.

Solution

Another way to do what Thorsten suggested:

select  e.*, 
        ( SELECT  group_concat(skill_id order by  skill_id separator ',')
            from  events_skills
            WHERE  event_id = e.id 
        ) AS skills, 
        ( SELECT  group_concat(user_id order by  user_id separator ',' )
            from  events_users
            WHERE  event_id = e.id 
        ) AS users
    from  events e
    order by  e.id;

Each subquery needs an extra join to get the "name" of the skill and user instead of the "id".

May I suggest you use the name of the skill as its id.

When you have a many-many mapping table, be sure to use the pair of ids as the PRIMARY KEY and have an INDEX in the opposite order. And don't have an AUTO_INCREMENT. Details: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table