how to formulate query (conditional)

Question

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

Answer 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.

• j10: 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: Yes we can do in 2 queries as you described - I was wondering if we could do in one single sql statement.
• jwolf: 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.
• j10: Can you check my query which I posted as the answer.
• j10: 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.

Answer 2

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