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
FROM
dghshrml4_sanctionedposts AS s
LEFT JOIN dghshrml4_providers AS p ON p.sanctionedpost_id= s.id
LEFT JOIN dghshrml4_facilities AS f ON f.id= s.facility_id
INNER JOIN dghshrml4_designations AS d ON d.id= s.designation_id
WHERE
s.deleted_at IS NOT NULL