0

We have following tables - Action, Employee and ActionAssignedTo

I am trying to write a query to fetch an Action with id : 10 that has been assigned to emp_id : 1000. the reply of query should also contain all the other assignees for the same action i.e. same action can be assigned to multiple people

select     Action.name,
           completed_At, 
           group_concat(AssigneeNameTable.name) as assignedTo, 
           group_concat(AssigneeNameTable.id) as assignedToId, 
           ActionAssignedTo.action_Id as actionId
from       Action

inner join Employee 
on         Action.emp_Id = Employee.id and Employee.vendor_Id = 1

inner join ActionAssignedTo 
on         Action.id = ActionAssignedTo.action_Id 
and        ActionAssignedTo.action_Id = 10 

inner join Employee as AssigneeNameTable 
on         ActionAssignedTo.emp_Id = AssigneeNameTable.Id 

where     ActionAssignedTo.emp_Id = 1000 
and       group by Action.id 
limit 2

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

Now, lets say we have a Action with id = 10 and its Assigned to 2 Employees with id: 1000 and id: 1001

ActionAssignedTo Table : 

action_Id |  emp_Id
-------------------
10        |  1000
10        |  1001

My current query only returns a single record with emp_Id id : 1000. How can I modify the query so that the query returns both the assigness but it also checks that the

ActionAssignedTo.emp_Id = 1000 (as only Employee who either created or are assigned that action can view the action).

1

If I understand your question, you should think about it this way:

Select aat.action_id, aat.emp_id
    from ActionAssignedTo aat
    where aat.action_id = 10

gives you the keys you want. You just expand that to include look-ups to the other tables to get the rest:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10

In your query, you are limiting the results to only action 10 in the second INNER JOIN and then further limiting the results to employee 1000 in the WHERE clause. It's not employee 1000 and all the other employees who were also assigned action 10; it's just all the employees who were assigned action 10 (employee 1000 will be among them). The solution (unless I'm totally misreading what you're trying to do) is fairly simple and your original approach was over-complicating things.

I hope this helps.

AMENDED:

I looked at your newly posted query. Still looks like it;s getting in it's own way but it made me wonder if you're trying to test for Employee 1000 as either the assigner or the assignee (since you're joining the employee table to itself again). So I added adding logic to do that additional test in the where clause on the off-chance that is the sticking point:

Select {whatever columns you want from any of the tables}
    from ActionAssignedTo aat
    inner join action a on a.id = aat.action_id
    inner join employee emp on emp.id = aat.emp_id
    where aat.action_id = 10
        and (aat.emp_Id = 1000 or a.emp_Id = 1000)

I'm inferring that ActionAssignedTo.emp_Id and action.emp_Id represent the assignee and assigner respectively - hope I got that right,

I hope this helps.

  • I do want to fetch data only for Action 10. However, I need all the Asssignees but at the same time I need to have a check like ActionAssignedTo.emp_Id = 1000 (as only Employee are assigned that action can view the action - business rule). – j10 Jan 19 '18 at 10:02
  • Yes we can do in 2 queries as you described - I was wondering if we could do in one single sql statement. – j10 Jan 19 '18 at 10:04
  • The second one IS the query (or at least the framework of it). The first query was just a stripped down version to demonstrate the underlying logic to get the necessary rows; expanding on it by adding the two inner joins gets you access to any of the columns in any of the three tables. – jwolf Jan 19 '18 at 19:17
  • Can you check my query which I posted as the answer. – j10 Jan 20 '18 at 6:22
  • Since this will be exposed via REST endpoint and a employee with id: empId will be making a request. We need to check the result set has a record where the ActionAssignedTo.assignee = empId(from http request) - Kind of Business rule. – j10 Jan 20 '18 at 6:25
0

I thought of taking another inner join on ActionAssignedTo as ATtable so we can fetch both the records. Am not sure if this a good way to formulate the query.

select     Action.name,
           completed_At, 
           group_concat(AssigneeNameTable.name) as assignedTo, 
           group_concat(AssigneeNameTable.id) as assignedToId, 
           ActionAssignedTo.action_Id as actionId
from       Action

inner join Employee 
on         Action.emp_Id = Employee.id and Employee.vendor_Id = 1

inner join ActionAssignedTo 
on         Action.id = ActionAssignedTo.action_Id 
and        ActionAssignedTo.action_Id = 10 

inner join ActionAssignedTo as ATtable 
on         ActionAssignedTo.action_Id = ATtable.action_Id

inner join Employee as AssigneeNameTable 
on         ATtable.emp_Id = AssigneeNameTable.Id 

where     ActionAssignedTo.emp_Id = 1000 
and       group by Action.id 
limit 2

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.