SELECT distinct bv.BeneficiaryRegID,bv.CreatedBy,
        mp.BenRegId,
            mp.createddate AS registrationdate,
            d.gender,
            d.FirstName,
            d.LastName,
            d.Status,
            d.maritalstatus,
            d.literacystatus,
            (case when d.DOB is not null then d.dob else JSON_UNQUOTE(JSON_EXTRACT(ExtraFields, '$.dob')) end ) AS dob,
 (case when d.incomeStatus is not null then d.incomeStatus else JSON_UNQUOTE(JSON_EXTRACT(ExtraFields, '$.incomeName')) end) AS incomestatus,
(case when d.occupation is not null then d.occupation else
  JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.occupationOther')) end) AS occupation,
  (case when d.education is not null then d.education else JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.educationQualificationName')) end) AS education,
( case when d.religion is not null then d.religion else JSON_UNQUOTE(JSON_EXTRACT(d.ExtraFields, '$.Religion')) End) AS religion,
           -- d.education,
           -- d.occupation,d.dob,
           -- d.incomestatus,
            d.community,
           -- d.religion,
            ad.CurrDistrict,
            ad.CurrVillage,
            ad.CurrServicePoint,
            mm.BeneficiaryID
    FROM db_iemr.t_benvisitdetail bv
      left join db_identity.i_beneficiarymapping mp on mp.benregid=bv.BeneficiaryRegID
        left join db_identity.m_beneficiaryregidmapping mm on mp.BenRegId=mm.BenRegId
    left JOIN  db_identity.i_beneficiarydetails d ON (mp.bendetailsid = d.BeneficiaryDetailsId)
    left JOIN db_identity.i_beneficiaryaddress ad ON (mp.BenAddressId = ad.BenAddressID)
    where bv.providerservicemapid=3 and mp.benregid is NULL