Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 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
Add Comment
Please, Sign In to add comment