CREATE TABLE IF NOT EXISTS `user_mst` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `name_full` varchar(250) DEFAULT NULL,
  `name_with_initials` varchar(250) DEFAULT NULL,
  `gender` char(1) NOT NULL,
  `nic_no` varchar(20) DEFAULT NULL,
  `title_id` varchar(10) NOT NULL,
  `dob` date DEFAULT NULL,
  `address1` varchar(100) DEFAULT NULL,
  `address2` varchar(100) DEFAULT NULL,
  `address3` varchar(100) DEFAULT NULL,
  `address4` varchar(100) DEFAULT NULL,
  `tele_no` varchar(40) DEFAULT NULL,
  `mob_no` varchar(40) DEFAULT NULL,
  `email_personal` varchar(100) DEFAULT NULL,
  `role_id` varchar(10) NOT NULL,
  `creator_user_id` varchar(50) NOT NULL,
  `active_user` tinyint(1) NOT NULL,
  `emp_no1` varchar(50) DEFAULT NULL,
  `emp_no2` varchar(50) DEFAULT NULL,
  `unit_id` varchar(50) DEFAULT NULL,
  `division_id` varchar(50) DEFAULT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email_official` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  KEY `fk_user_mst_title_mst` (`title_id`),
  KEY `fk_user_mst_role_mst1` (`role_id`),
  KEY `fk_user_mst_unit_mst1` (`unit_id`,`division_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

INSERT 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
(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'),
(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'),
(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');

  CREATE TABLE IF NOT EXISTS `division_mst` (
  `division_id` varchar(50) NOT NULL,
  `division_code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `colour` varchar(50) DEFAULT NULL,
  `head_user_id` int(11) DEFAULT NULL,
  `assistant_1_user_id` int(11) DEFAULT NULL,
  `assistant_2_user_id` int(11) DEFAULT NULL,
  `main_division_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`division_id`),
  KEY `fk_division_mst_user_mst1` (`head_user_id`),
  KEY `fk_division_mst_user_mst2` (`assistant_1_user_id`),
  KEY `fk_division_mst_user_mst3` (`assistant_2_user_id`),
  KEY `fk_division_mst_main_division_mst1` (`main_division_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT 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
('1', 'D001', 'Administration', 'tjrtujrt', 'pink', 1, 2, NULL, 1),
('2', 'D002n', 'Human Resourcen', 'yjghkhk', 'red', 1, 3, 2, 1),
('3', 'D003', 'Marketing', 'jhghfg', 'green', 2, 1, 3, 2),
('4', 'D004', 'IT ', NULL, NULL, NULL, NULL, NULL, NULL),
('5', 'D005', 'Accounting ', NULL, NULL, NULL, NULL, NULL, NULL);

select
    d.*,
    u1.name_full AS head_user,
    u2.name_full AS assistant1,
    u3.name_full AS assistant2
from division_mst d
    LEFT OUTER JOIN user_mst u1 ON d.head_user_id=u1.user_id
    LEFT OUTER JOIN user_mst u2 ON d.assistant_1_user_id=u2.user_id
    LEFT OUTER JOIN user_mst u3 ON d.assistant_2_user_id=u3.user_id

SELECT  b.`name_full` head_user,
        c.`name_full` assistant_1,
        d.`name_full assistant_2
FROM    `division_mst` a
        INNER JOIN `user_mst` b
            ON a.`head_user_id` = b.`user_id`
        INNER JOIN `user_mst` c
            ON a.`assistant_1_user_id` = b.`user_id`
        INNER JOIN `user_mst` d
            ON a.`assistant_2_user_id` = b.`user_id`

CONSTRAINT `fk_division_mst_user_mst1` FOREIGN KEY (`head_user_id`) REFERENCES `user_mst`(user_id),
  CONSTRAINT `fk_division_mst_user_mst2` FOREIGN KEY (`assistant_1_user_id`) REFERENCES `user_mst`(user_id),
  CONSTRAINT `fk_division_mst_user_mst3` FOREIGN KEY (`assistant_2_user_id`) REFERENCES `user_mst`(user_id)