SELECT
	`s`.`id` AS `s_id`,
	`s`.`name` AS `s_name`,
	`s`.`facility_id` AS `s_facility_id`,
	`s`.`designation_id` AS `s_designation_id`,
	`s`.`facilityagency_id` AS `s_facilityagency_id`,
	`s`.`facilityagency_code` AS `s_facilityagency_code`,
	`s`.`facilityagency_name` AS `s_facilityagency_name`,
	`s`.`sanctionedpostfirstleveloption_id` AS `s_sanctionedpostfirstleveloption_id`,
	`s`.`sanctionedpostfirstleveloption_code` AS `s_sanctionedpostfirstleveloption_code`,
	`s`.`sanctionedpostfirstleveloption_name` AS `s_sanctionedpostfirstleveloption_name`,
	`s`.`sanctionedpostsecondleveloption_id` AS `s_sanctionedpostsecondleveloption_id`,
	`s`.`sanctionedpostsecondleveloption_code` AS `s_sanctionedpostsecondleveloption_code`,
	`s`.`sanctionedpostsecondleveloption_name` AS `s_sanctionedpostsecondleveloption_name`,
	`s`.`sanctionedposttype_id` AS `s_sanctionedposttype_id`,
	`s`.`sanctionedposttype_code` AS `s_sanctionedposttype_code`,
	`s`.`sanctionedposttype_name` AS `s_sanctionedposttype_name`,
	`p`.`id` AS `p_id`,
	`p`.`name` AS `p_name`,
	`p`.`name_BN` AS `p_name_BN`,
	`p`.`code` AS `p_code`,
	`p`.`pds_code` AS `p_pds_code`,
	`p`.`dob` AS `p_dob`,
	`p`.`national_id_no` AS `p_national_id_no`,
	`p`.`retirement_date` AS `p_retirement_date`,
	`p`.`fathers_name` AS `p_fathers_name`,
	`p`.`mothers_name` AS `p_mothers_name`,
	`p`.`email` AS `p_email`,
	`p`.`contact_no` AS `p_contact_no`,
	`p`.`is_freedomfighter` AS `p_is_freedomfighter`,
	`p`.`joining_date_govt_health_service` AS `p_joining_date_govt_health_service`,
	`p`.`joining_date_current_place` AS `p_joining_date_current_place`,
	`p`.`joining_date_current_designation` AS `p_joining_date_current_designation`,
	`p`.`tribaloption_id` AS `p_tribaloption_id`,
	`p`.`tribaloption_name` AS `p_tribaloption_name`,
	`p`.`sex_id` AS `p_sex_id`,
	`p`.`sex_name` AS `p_sex_name`,
	`p`.`maritalstatus_id` AS `p_maritalstatus_id`,
	`p`.`maritalstatus_name` AS `p_maritalstatus_name`,
	`p`.`religiousgroup_name` AS `p_religiousgroup_name`,
	`p`.`professionaldiscipline_id` AS `p_professionaldiscipline_id`,
	`p`.`professionaldiscipline_name` AS `p_professionaldiscipline_name`,
	`p`.`department_id` AS `p_department_id`,
	`p`.`department_name` AS `p_department_name`,
	`p`.`staffpostingoption_id` AS `p_staffpostingoption_id`,
	`p`.`staffpostingoption_name` AS `p_staffpostingoption_name`,
	`p`.`staffprofessionalcategory_id` AS `p_staffprofessionalcategory_id`,
	`p`.`staffprofessionalcategory_name` AS `p_staffprofessionalcategory_name`,
	`p`.`providerclass_id` AS `p_providerclass_id`,
	`p`.`providerclass_name` AS `p_providerclass_name`,
	`p`.`designationposttype_id` AS `p_designationposttype_id`,
	`p`.`designationposttype_name` AS `p_designationposttype_name`,
	`p`.`drawsalaryplacetype_id` AS `p_drawsalaryplacetype_id`,
	`p`.`drawsalaryplacetype_name` AS `p_drawsalaryplacetype_name`,
	`p`.`drawsalarytype_id` AS `p_drawsalarytype_id`,
	`p`.`drawsalarytype_name` AS `p_drawsalarytype_name`,
	`p`.`designationtypeoption_id` AS `p_designationtypeoption_id`,
	`p`.`designationtypeoption_name` AS `p_designationtypeoption_name`,
	`p`.`postedasoption_id` AS `p_postedasoption_id`,
	`p`.`postedasoption_name` AS `p_postedasoption_name`,
	`p`.`workingstatus_id` AS `p_workingstatus_id`,
	`p`.`workingstatus_name` AS `p_workingstatus_name`,
	`p`.`has_govt_quarter` AS `p_has_govt_quarter`,
	`p`.`is_medical` AS `p_is_medical`,
	`p`.`last_promotion_confirmed_designation` AS `p_last_promotion_confirmed_designation`,
	`p`.`last_promotion_go` AS `p_last_promotion_go`,
	`p`.`last_promotion_serial` AS `p_last_promotion_serial`,
	`p`.`last_promotion_date` AS `p_last_promotion_date`,
	`p`.`desired_designation_group` AS `p_desired_designation_group`,
	`p`.`desired_professional_discipline` AS `p_desired_professional_discipline`,
	`p`.`ist_year` AS `p_ist_year`,
	`p`.`ist_appointment_date` AS `p_ist_appointment_date`,
	`p`.`ist_go` AS `p_ist_go`,
	`p`.`ist_sl_no` AS `p_ist_sl_no`,
	`p`.`ist_date` AS `p_ist_date`,
	`p`.`ist_appointment_go` AS `p_ist_appointment_go`,
	`p`.`ist_appointment_sl_no` AS `p_ist_appointment_sl_no`,
	`p`.`bcs_batch_no` AS `p_bcs_batch_no`,
	`p`.`bcs_psc_regularization_date` AS `p_bcs_psc_regularization_date`,
	`p`.`bcs_psc_regularization_go` AS `p_bcs_psc_regularization_go`,
	`p`.`bcs_psc_regularization_sl_no` AS `p_bcs_psc_regularization_sl_no`,
	`p`.`adhoc_go_no` AS `p_adhoc_go_no`,
	`p`.`adhoc_sl_no` AS `p_adhoc_sl_no`,
	`p`.`adhoc_sl_date` AS `p_adhoc_sl_date`,
	`p`.`service_confirmation_date` AS `p_service_confirmation_date`,
	`p`.`service_confirmation_go` AS `p_service_confirmation_go`,
	`p`.`service_confirmation_sl_no` AS `p_service_confirmation_sl_no`,
	`p`.`is_senior_scale_pass` AS `p_is_senior_scale_pass`,
	`p`.`is_departmental_exam_pass` AS `p_is_departmental_exam_pass`,
	`p`.`experience_in_village` AS `p_experience_in_village`,
	`p`.`sanctionedpost_id` AS `p_sanctionedpost_id`,
	`p`.`current_basic_pay_taka` AS `p_current_basic_pay_taka`,
	`p`.`current_payscale` AS `p_current_payscale`,
	`p`.`prlorder_id` AS `p_prlorder_id`,
	`p`.`from_sanctionedpost_id` AS `p_from_sanctionedpost_id`,
	`p`.`from_sanctionedpost_details` AS `p_from_sanctionedpost_details`,
	`p`.`prl_start_date` AS `p_prl_start_date`,
	`p`.`prension_received_date` AS `p_prension_received_date`,
	`p`.`retirementtype_id` AS `p_retirementtype_id`,
	`p`.`retirementtype_name` AS `p_retirementtype_name`,
	`p`.`nominee_name` AS `p_nominee_name`,
	`p`.`nominee_fathers_name` AS `p_nominee_fathers_name`,
	`p`.`nominee_nid` AS `p_nominee_nid`,
	`p`.`nominee_hid` AS `p_nominee_hid`,
	`p`.`providerstatus_code` AS `p_providerstatus_code`,
	`p`.`providerstatus_name` AS `p_providerstatus_name`,
	`p`.`has_acr` AS `p_has_acr`,
	`p`.`acr_year` AS `p_acr_year`,
	`p`.`primary_designation` AS `p_primary_designation`,
	`p`.`joining_date_primary_designation` AS `p_joining_date_primary_designation`,
	`p`.`attendance_id` AS `p_attendance_id`,
	`f`.`name` AS `f_name`,
	`f`.`name_BN` AS `f_name_BN`,
	`f`.`code` AS `f_code`,
	`f`.`division_id` AS `f_division_id`,
	`f`.`division_code` AS `f_division_code`,
	`f`.`division_name` AS `f_division_name`,
	`f`.`district_id` AS `f_district_id`,
	`f`.`district_code` AS `f_district_code`,
	`f`.`district_name` AS `f_district_name`,
	`f`.`upazila_id` AS `f_upazila_id`,
	`f`.`upazila_code` AS `f_upazila_code`,
	`f`.`upazila_name` AS `f_upazila_name`,
	`f`.`paurasava_id` AS `f_paurasava_id`,
	`f`.`paurasava_code` AS `f_paurasava_code`,
	`f`.`paurasava_name` AS `f_paurasava_name`,
	`f`.`union_id` AS `f_union_id`,
	`f`.`union_code` AS `f_union_code`,
	`f`.`union_name` AS `f_union_name`,
	`f`.`ward_id` AS `f_ward_id`,
	`f`.`ward_code` AS `f_ward_code`,
	`f`.`ward_name` AS `f_ward_name`,
	`f`.`village_code` AS `f_village_code`,
	`f`.`house_number` AS `f_house_number`,
	`f`.`mailing_address` AS `f_mailing_address`,
	`f`.`facilitytype_id` AS `f_facilitytype_id`,
	`f`.`facilitytype_name` AS `facilitytype_name`,
	`f`.`facilityagency_id` AS `f_facilityagency_id`,
	`f`.`facilityagency_name` AS `f_facilityagency_name`,
	`f`.`facilityfunction_id` AS `f_facilityfunction_id`,
	`f`.`facilityfunction_name` AS `f_facilityfunction_name`,
	`f`.`facilitylevel_id` AS `f_facilitylevel_id`,
	`f`.`facilitylevel_name` AS `f_facilitylevel_name`,
	`f`.`facilityhealthcarelevel_id` AS `f_facilityhealthcarelevel_id`,
	`f`.`facilityhealthcarelevel_name` AS `f_facilityhealthcarelevel_name`,
	`f`.`facilitylocationtype_id` AS `f_facilitylocationtype_id`,
	`f`.`facilitylocationtype_name` AS `f_facilitylocationtype_name`,
	`f`.`facilityownership_id` AS `f_facilityownership_id`,
	`f`.`facilityownership_name` AS `f_facilityownership_name`,
	`f`.`is_active` AS `f_is_active`,
	`f`.`total_posts_reported` AS `f_total_posts_reported`,
	`f`.`filled_posts_reported` AS `f_filled_posts_reported`,
	`f`.`vacant_posts_reported` AS `f_vacant_posts_reported`,
	`d`.`id` AS `d_id`,
	`d`.`name` AS `d_name`,
	`d`.`name_BN` AS `d_name_BN`,
	`d`.`code` AS `d_code`,
	`d`.`rank` AS `d_rank`,
	`d`.`designation_primary_category` AS `d_designation_primary_category`,
	`d`.`reporting_category` AS `d_reporting_category`,
	`d`.`designationpayscale_id` AS `d_designationpayscale_id`,
	`d`.`designationpayscale_code` AS `d_designationpayscale_code`,
	`d`.`designationpayscale_name` AS `d_designationpayscale_name`,
	`d`.`designationclass_id` AS `d_designationclass_id`,
	`d`.`designationclass_code` AS `d_designationclass_code`,
	`d`.`designationclass_name` AS `d_designationclass_name`,
	`d`.`designationdiscipline_id` AS `d_designationdiscipline_id`,
	`d`.`designationdiscipline_code` AS `d_designationdiscipline_code`,
	`d`.`designationdiscipline_name` AS `d_designationdiscipline_name`,
	`d`.`designationgroup_id` AS `d_designationgroup_id`,
	`d`.`designationgroup_code` AS `d_designationgroup_code`,
	`d`.`designationgroup_name` AS `d_designationgroup_name`,
	`d`.`designationbdprofcategory_id` AS `d_designationbdprofcategory_id`,
	`d`.`designationbdprofcategory_code` AS `d_designationbdprofcategory_code`,
	`d`.`designationbdprofcategory_name` AS `d_designationbdprofcategory_name`,
	`d`.`designationwhoprofgroup_id` AS `d_designationwhoprofgroup_id`,
	`d`.`designationwhoprofgroup_code` AS `d_designationwhoprofgroup_code`,
	`d`.`designationwhoprofgroup_name` AS `d_designationwhoprofgroup_name`,
	`d`.`group_code` AS `d_group_code`,
	`d`.`is_healthworker` AS `d_is_healthworker`,
	`d`.`is_active` AS `d_is_active`,
	`p`.`religiousgroup_id` AS `p_religiousgroup_id`,
	`p`.`is_active` AS `p_is_active`,
	`s`.`is_active` AS `s_is_active`,
	`f`.`id` AS `f_id`
FROM
	(
		(
			(
				`dghshrml4_sanctionedposts` `s`
				LEFT JOIN `dghshrml4_providers` `p` ON (
					(
						`p`.`sanctionedpost_id` = `s`.`id`
					)
				)
			)
			LEFT JOIN `dghshrml4_designations` `d` ON (
				(
					`s`.`designation_id` = `d`.`id`
				)
			)
		)
		LEFT JOIN `dghshrml4_facilities` `f` ON (
			(`s`.`facility_id` = `f`.`id`)
		)
	)
WHERE
	isnull(`s`.`deleted_at`)