Mysql – how to formulate query (conditional)

MySQLmysql-5.7

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

Best Answer

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.

Related Question