How to merge records for query set

Question

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.

score 1 · Accepted Answer · answered Jan 16 '18 at 10:14

You can get it by using group_concat()

create table t (notes varchar(200), id int, name varchar(100), emp_Id int);

insert into t values ('1. Finish Designing Queries', 10, 'John', 2);
insert into t values ('2. Finish Designing Queries', 10, 'Jack', 5);
insert into t values ('3. Other note', 20, 'Jack', 5);

select notes, id, group_concat(name, ':', emp_id)
from   t
group by id;

notes                       | id | group_concat(name, ':', emp_id)
:-------------------------- | -: | :------------------------------
1. Finish Designing Queries | 10 | John:2,Jack:5                  
3. Other note               | 20 | Jack:5

dbfiddle here

score 1 · Answer 2 · answered Jan 16 '18 at 11:33

This is not an answer to your question, but it was a bit long for a comment so I'll add it here. If not mistaken, since:

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))

we can rewrite this to:

    on         (Action.emp_Id = Employee.id 
    and        Employee.id in (select Employee.id 
                         from Employee 
                         where Employee.vendor_Id = 1))

and:

    on         (Action.emp_Id = Employee.id 
    and        Employee.vendor_Id = 1

Which should save you one table access to Employee.

How to merge records for query set

2 Answers2

Linked

Related