register get-all queries

join.sql

select
	p.uuid p_uuid,
	cp."uuid" cp_uuid,
	co.unit_type, 
	p.id p_id,
	p.party_type p_party_type,
	p.name p_name,
	p.person_identifier p_person_identifier,
	p.organization_identifier p_organization_identifier,
	p.created p_created,
	p.updated p_updated,
	f.first_name p_first_name,
	f.middle_name p_middle_name,
	f.last_name p_last_name,
	f.address p_address,
	f.mailing_address p_person_mailing_address,
	f.date_of_birth p_date_of_birth,
	f.date_of_death p_date_of_death,
	o.unit_status p_unit_status,
	o.unit_type p_unit_type,
	o.telephone_number p_telephone_number,
	o.mobile_number p_mobile_number,
	o.fax_number p_fax_number,
	o.email_address p_email_address,
	o.internet_address p_internet_address,
	o.mailing_address p_org_mailing_address,
	o.business_address p_business_address
from register.party p
full join register.organization o using (uuid)
full join register.person f using (uuid)
left join register.external_role r on p.uuid = r.to_party and r."source" = 'ccr' and (r.identifier = 'aafy' or r.identifier = 'bedr')
left join register.party cp on cp."uuid" = r.from_party 
left join register.organization co on co."uuid" = cp."uuid" 
where p.uuid = 'b6368d0a-bce4-4798-8460-f4f86fc354c2'
--where p.uuid = '08cb91ff-75a4-45a4-b141-3c6be1bf8728'
order by p.uuid

sub-query.sql

select
	p.uuid p_uuid,
	cp.uuid cp_uuid,
	cp.unit_type cp_unit_type, 
	p.id p_id,
	p.party_type p_party_type,
	p.name p_name,
	p.person_identifier p_person_identifier,
	p.organization_identifier p_organization_identifier,
	p.created p_created,
	p.updated p_updated,
	f.first_name p_first_name,
	f.middle_name p_middle_name,
	f.last_name p_last_name,
	f.address p_address,
	f.mailing_address p_person_mailing_address,
	f.date_of_birth p_date_of_birth,
	f.date_of_death p_date_of_death,
	o.unit_status p_unit_status,
	o.unit_type p_unit_type,
	o.telephone_number p_telephone_number,
	o.mobile_number p_mobile_number,
	o.fax_number p_fax_number,
	o.email_address p_email_address,
	o.internet_address p_internet_address,
	o.mailing_address p_org_mailing_address,
	o.business_address p_business_address
from register.party p
full join register.organization o using (uuid)
full join register.person f using (uuid)
left join (
  select 
  	cp.uuid,
	cp.id,
	cp.party_type,
	cp.name,
	cp.person_identifier,
	cp.organization_identifier,
	cp.created,
	cp.updated,
	co.unit_status,
	co.unit_type,
	co.telephone_number,
	co.mobile_number,
	co.fax_number,
	co.email_address,
	co.internet_address,
	co.mailing_address,
	co.business_address,
	r.to_party parent_uuid
  from register.external_role r
  full join register.party cp on cp."uuid" = r.from_party
  full join register.organization co using (uuid)
  where r."source" = 'ccr' and (r.identifier = 'aafy' or r.identifier = 'bedr')
) cp on cp.parent_uuid = p.uuid
where p.uuid = 'b6368d0a-bce4-4798-8460-f4f86fc354c2'
--where p.uuid = '08cb91ff-75a4-45a4-b141-3c6be1bf8728'
order by p.uuid