· 7 years ago · Feb 22, 2019, 02:38 PM
1CREATE TABLE IF NOT EXISTS `user_mst` (
2 `user_id` int(11) NOT NULL AUTO_INCREMENT,
3 `name_full` varchar(250) DEFAULT NULL,
4 `name_with_initials` varchar(250) DEFAULT NULL,
5 `gender` char(1) NOT NULL,
6 `nic_no` varchar(20) DEFAULT NULL,
7 `title_id` varchar(10) NOT NULL,
8 `dob` date DEFAULT NULL,
9 `address1` varchar(100) DEFAULT NULL,
10 `address2` varchar(100) DEFAULT NULL,
11 `address3` varchar(100) DEFAULT NULL,
12 `address4` varchar(100) DEFAULT NULL,
13 `tele_no` varchar(40) DEFAULT NULL,
14 `mob_no` varchar(40) DEFAULT NULL,
15 `email_personal` varchar(100) DEFAULT NULL,
16 `role_id` varchar(10) NOT NULL,
17 `creator_user_id` varchar(50) NOT NULL,
18 `active_user` tinyint(1) NOT NULL,
19 `emp_no1` varchar(50) DEFAULT NULL,
20 `emp_no2` varchar(50) DEFAULT NULL,
21 `unit_id` varchar(50) DEFAULT NULL,
22 `division_id` varchar(50) DEFAULT NULL,
23 `username` varchar(50) NOT NULL,
24 `password` varchar(50) NOT NULL,
25 `email_official` varchar(45) DEFAULT NULL,
26 PRIMARY KEY (`user_id`),
27 KEY `fk_user_mst_title_mst` (`title_id`),
28 KEY `fk_user_mst_role_mst1` (`role_id`),
29 KEY `fk_user_mst_unit_mst1` (`unit_id`,`division_id`)
30) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
31
32INSERT INTO `user_mst` (`user_id`, `name_full`, `name_with_initials`, `gender`, `nic_no`, `title_id`, `dob`, `address1`, `address2`, `address3`, `address4`, `tele_no`, `mob_no`, `email_personal`, `role_id`, `creator_user_id`, `active_user`, `emp_no1`, `emp_no2`, `unit_id`, `division_id`, `username`, `password`, `email_official`) VALUES
33(2, 'dss', 'ddsf', 'm', '2353246565v', '12', '2012-03-12', 'ewtewt', 'ryery', 'ertert', 'wetwet', '325235325', '23523534523', 'fdrg@yahoo.com', '1', '1', 1, '', NULL, '1', '1', 'cde', '123', 'sasas@gmail.com'),
34(3, 'wrwer', 'egrt', 'f', '2432544663', '12', '2012-03-26', 'erwerw', 'wetw', 'ewtwe', 'ewtw', '132435435', '1243345345', 'dfggdfg', '23', '1', 1, '12', '12', '12', '3', 'pas', '123', 'sdasda'),
35(4, 'asd', 'asd', 'f', '5671234676V', '12', '2012-03-05', 'sdgdsgsd', 'sdgsdgds', 'rgwergwetg', 'ergry', '12141242145', '1242135346', 'prameeshas@yahoo.com', '1', '123567', 1, '1234', '123', '1', '1', 'abc', '234', 'prameeshas@yahoo.com');
36
37 CREATE TABLE IF NOT EXISTS `division_mst` (
38 `division_id` varchar(50) NOT NULL,
39 `division_code` varchar(50) NOT NULL,
40 `name` varchar(100) NOT NULL,
41 `description` varchar(500) DEFAULT NULL,
42 `colour` varchar(50) DEFAULT NULL,
43 `head_user_id` int(11) DEFAULT NULL,
44 `assistant_1_user_id` int(11) DEFAULT NULL,
45 `assistant_2_user_id` int(11) DEFAULT NULL,
46 `main_division_id` int(11) DEFAULT NULL,
47 PRIMARY KEY (`division_id`),
48 KEY `fk_division_mst_user_mst1` (`head_user_id`),
49 KEY `fk_division_mst_user_mst2` (`assistant_1_user_id`),
50 KEY `fk_division_mst_user_mst3` (`assistant_2_user_id`),
51 KEY `fk_division_mst_main_division_mst1` (`main_division_id`)
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53
54INSERT INTO `division_mst` (`division_id`, `division_code`, `name`, `description`, `colour`, `head_user_id`, `assistant_1_user_id`, `assistant_2_user_id`, `main_division_id`) VALUES
55('1', 'D001', 'Administration', 'tjrtujrt', 'pink', 1, 2, NULL, 1),
56('2', 'D002n', 'Human Resourcen', 'yjghkhk', 'red', 1, 3, 2, 1),
57('3', 'D003', 'Marketing', 'jhghfg', 'green', 2, 1, 3, 2),
58('4', 'D004', 'IT ', NULL, NULL, NULL, NULL, NULL, NULL),
59('5', 'D005', 'Accounting ', NULL, NULL, NULL, NULL, NULL, NULL);
60
61select
62 d.*,
63 u1.name_full AS head_user,
64 u2.name_full AS assistant1,
65 u3.name_full AS assistant2
66from division_mst d
67 LEFT OUTER JOIN user_mst u1 ON d.head_user_id=u1.user_id
68 LEFT OUTER JOIN user_mst u2 ON d.assistant_1_user_id=u2.user_id
69 LEFT OUTER JOIN user_mst u3 ON d.assistant_2_user_id=u3.user_id
70
71SELECT b.`name_full` head_user,
72 c.`name_full` assistant_1,
73 d.`name_full assistant_2
74FROM `division_mst` a
75 INNER JOIN `user_mst` b
76 ON a.`head_user_id` = b.`user_id`
77 INNER JOIN `user_mst` c
78 ON a.`assistant_1_user_id` = b.`user_id`
79 INNER JOIN `user_mst` d
80 ON a.`assistant_2_user_id` = b.`user_id`
81
82CONSTRAINT `fk_division_mst_user_mst1` FOREIGN KEY (`head_user_id`) REFERENCES `user_mst`(user_id),
83 CONSTRAINT `fk_division_mst_user_mst2` FOREIGN KEY (`assistant_1_user_id`) REFERENCES `user_mst`(user_id),
84 CONSTRAINT `fk_division_mst_user_mst3` FOREIGN KEY (`assistant_2_user_id`) REFERENCES `user_mst`(user_id)