We have a question related to a DB query :

CREATE TABLE `Action` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_Id` int(10) unsigned NOT NULL,
  `name` varchar(60) NOT NULL,
  `updated_action_at` datetime(3) DEFAULT NULL,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`id`),
  
  KEY `action_empId_fk` (`emp_Id`),
 
  CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
  
) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1


CREATE TABLE `ActionAssignedTo` (
  `action_Id` int(10) unsigned DEFAULT NULL,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  KEY `actionassignedto_emp_id_foreign` (`emp_Id`),
  KEY `actionassignedto_action_id_foreign` (`action_Id`),
  CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `mobile_Number` varchar(15) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

We want to find the action created by employee with Action.empId = 4 along with the list of Assignee (an action can be assigned to multiple people)

We have the following query :

Select notes, Action.id, AssigneeName.name, ActionAssignedTo.emp_Id from Employee
inner join Action on (Action.emp_Id = Employee.id and
Action.emp_Id in ( select Employee.id from Employee where Employee.vendor_Id = 1))

inner join ActionAssignedTo on Action.id =  ActionAssignedTo.action_Id
inner join Employee as AssigneeName on ActionAssignedTo.emp_Id =  AssigneeName.id
where Action.emp_Id = 4

Now, lets say if Action with id = 10 was assigned to 2 people : John (empId : 2) And Jack (empId : 5) --> the above query returns following results :

(columns from the query) notes | Action.id | AssigneeName.name | ActionAssignedTo.emp_Id

1. Finish Designing Queries |10 | John | 2
2. Finish Designing Queries| 10 | Jack | 5

If we observe the above results the first 2 columns get repeated for the Assignees for SAME ACTION ID --> is there a way we can merge them something like

1. Finish Designing Queries |10 | { John : 2 , Jack : 5}

Basically records being merged into one.

We are using MySQL 5.7