0

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.

1

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

  • Thank you @McNets. I will try this code in my db. Just keen to know does group_concat cause any perf issues. – j10 Jan 16 '18 at 11:45
  • 1
    I'm glad to help – McNets Jan 16 '18 at 11:47
  • In the above example if I only wanted name field, so I just use group_concat(name) --> Is there a way we can get this in form of array rather than comma separated values – j10 Mar 23 '18 at 9:44
  • On MySQL 5.7 you can use JSON_ARRAY, unfortunately I can't use this fiddle to show up an example. – McNets Mar 23 '18 at 9:48
  • 1
    @j10 have a look at this: rextester.com/XMTM70410 – McNets Mar 23 '18 at 11:48
1

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.

  • good suggestion @Lennart – j10 Jan 16 '18 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.