qry
SELECT
ri.*,
CONCAT(HF.hr_prefix_name,HP.hr_fname,' ',HP.hr_lname) AS fullname,
CONCAT(rip.HR_POSITION,'',IFNULL(rip.HR_LAVEL,'')) AS HR_PERSON_LAVEL,
CONCAT(ri.USER_POST_POSITION,'',IFNULL(HLP.HR_LEVEL_NAME,'')) AS HR_PERSONP_LEVEL,
year(date_go)+543 AS date_go_th,year(date_back)+543 AS date_back_th,
year(date_save)+543 AS DATE_SAVE_TH,hd.book_num AS book_num_dep,
hd.PHONE_IN AS phone_in_dep,
CONCAT(ri.LEADER_HR_POSITION,'',IFNULL(LHL.HR_LEVEL_NAME,'')) AS HR_LEADER_LEVEL,
CONCAT(LHF.HR_PREFIX_NAME,'',IFNULL(ri.LEADER_HR_NAME,'')) AS FULL_LEADER_HR_NAME
FROM
record_index ri
INNER JOIN record_index_person rip ON ri.id = rip.record_id
INNER JOIN hr_person HP ON rip.hr_person_id = HP.id
LEFT OUTER JOIN hr_person HP1 ON ri.USER_POST = HP1.id
LEFT OUTER JOIN hr_person LHP1 ON ri.LEADER_HR_ID = LHP1.ID
INNER JOIN hr_prefix HF ON HP.hr_prefix_id = HF.hr_prefix_id
LEFT OUTER JOIN hr_prefix LHF ON LHP1.HR_PREFIX_ID = LHF.HR_PREFIX_ID
INNER JOIN hr_department hd ON HP.hr_department_id=hd.hr_department_id
LEFT OUTER JOIN hr_level HLP ON HP1.HR_LEVEL_ID = HLP.HR_LEVEL_ID
LEFT OUTER JOIN hr_level LHL ON LHP1.HR_LEVEL_ID = LHL.HR_LEVEL_ID
LEFT OUTER JOIN hr_position HPP ON HP.HR_POSITION_ID=HPP.HR_POSITION_ID
WHERE ri.id=11111
GROUP BY rip.ID
/* ไปราชการในจังหวัดคนเดียว */
SELECT
HL.HR_LEVEL_NAME,ri.*,
CONCAT(HF.hr_prefix_name,HP.hr_fname,' ',HP.hr_lname) AS FULLNAME,
year(date_go)+543 AS date_go_th,year(date_back)+543 AS date_back_th,
year(date_save)+543 AS DATE_SAVE_TH,hd.book_num AS book_num_dep,
hd.PHONE_IN AS phone_in_dep,
CONCAT(HPP.HR_POSITION_NAME,'',IFNULL(HL.HR_LEVEL_NAME,'')) AS HR_PERSON_LEVEL,
CONCAT(ri.LEADER_HR_POSITION,'',IFNULL(LHL.HR_LEVEL_NAME,'')) AS HR_LEADER_LEVEL,
CONCAT(LHF.HR_PREFIX_NAME,'',IFNULL(ri.LEADER_HR_NAME,'')) AS FULL_LEADER_HR_NAME
FROM
record_index ri
LEFT OUTER JOIN hr_person HP ON ri.USER_POST = HP.id
LEFT OUTER JOIN hr_person LHP ON ri.LEADER_HR_ID = LHP.ID
LEFT OUTER JOIN hr_prefix HF ON HP.hr_prefix_id = HF.hr_prefix_id
LEFT OUTER JOIN hr_prefix LHF ON LHP.HR_PREFIX_ID = LHF.HR_PREFIX_ID
LEFT OUTER JOIN hr_department hd ON HP.hr_department_id=hd.hr_department_id
LEFT OUTER JOIN hr_level HL ON HP.HR_LEVEL_ID=HL.HR_LEVEL_ID
LEFT OUTER JOIN hr_level LHL ON LHP.HR_LEVEL_ID = LHL.HR_LEVEL_ID
LEFT OUTER JOIN hr_position HPP ON HP.HR_POSITION_ID=HPP.HR_POSITION_ID
WHERE ri.id=11111