db

with parceldetails as (
select
    pv.upi,
    cp.province_code AS provincecode,
    cp.province_name AS provincename,
    cd.district_code AS districtcode,
    cd.district_name AS districtname,
    cs.sector_code AS sectorcode,
    cs.sector_name AS sectorname,
    cc.cell_code AS cellcode,
    cc.cell_name AS cellname,
    p.village as villagename,
    p.village_code as villagecode,
    pv.area AS area,
    clu.land_use_type_id AS codeusage,
    clu.land_use_name_kinyarwanda AS descriptionusage,
    cr.right_type_name as titledeed
     FROM admin.parcel_version pv
   LEFT JOIN admin.catalog_province cp ON 'substring'(pv.upi, 1, 1) = 'substring'(cp.province_code, 2, 1)
   LEFT JOIN admin.catalog_district cd ON 'substring'(replace(pv.upi, '/', ''), 1, 3) = cd.district_code
   LEFT JOIN admin.catalog_sector cs ON 'substring'(replace(pv.upi, '/', ''), 1, 5) = cs.sector_code
   LEFT JOIN admin.catalog_cell cc ON 'substring'(replace(pv.upi, '/', ''), 1, 7) = cc.cell_code
   LEFT JOIN admin.catalog_land_use clu ON clu.land_use_type_id = pv.land_use_type_id
   LEFT JOIN sde.parcel p on p.upi=pv.upi
   LEFT JOIN admin.catalog_right cr on cr.right_type_id=pv.right_type_id where pv.upi='1/02/11/03/454'
), parcelowner as (
select rr.upi,
CASE
   WHEN pa.party_type = 'NON_NATURAL_PERSON' THEN nnp.full_name
   WHEN pa.party_type = 'NATURAL_PERSON' THEN concat(np.surname, ' ', np.forenames)
   ELSE NULL
   END AS ownernames,

 CASE
   WHEN pa.party_type = 'NON_NATURAL_PERSON' THEN nnp.id_no
   WHEN pa.party_type = 'NATURAL_PERSON' THEN np.id_no
   ELSE NULL
   END AS ownerid,
   pa.party_type
   FROM admin.real_right rr
   LEFT JOIN admin.natural_person_version np on np.party_id=rr.party_id
   LEFT JOIN admin.non_natural_person_version nnp on nnp.party_id=rr.party_id
   LEFT JOIN admin.party pa on pa.party_id=rr.party_id
   where  rr.end_transaction_id is null and (np.end_transaction_id is null)
), parcelrep as (
select pr.upi,
  concat(np.surname, ' ', np.forenames) AS representativenames,
   np.id_no AS representativeid from admin.object_representative pr
   left join admin.natural_person_version np on np.party_id=pr.representative_party_id
   LEFT JOIN admin.party pa on pa.party_id=pr.representative_party_id
   where pr.upi='1/02/11/03/454' and pr.end_transaction_id is null
   ), status as (

   select min(t.id),rr.upi,t.receive_date as registrationdate from admin.real_right rr left join admin.transaction t on t.transaction_id=rr.start_transaction_id
   where rr.upi='1/02/11/03/454' and rr.end_transaction_id is null and t.status in ('APPROVED','COMPLETED','PRINTED')
   group by rr.upi,t.receive_date)

SELECT distinct parceldetails.*, parcelowner.*, parcelrep.*,status.registrationdate from parceldetails inner join parcelowner on parcelowner.upi= parceldetails.upi inner join parcelrep on parcelrep.upi=parcelowner.upi inner join status on status.upi=parcelowner.upi