Skip to content

Instantly share code, notes, and snippets.

@Alxandr
Created September 6, 2024 12:56
Show Gist options
  • Save Alxandr/7f5f134fe3a49578547c98fe15264079 to your computer and use it in GitHub Desktop.
Save Alxandr/7f5f134fe3a49578547c98fe15264079 to your computer and use it in GitHub Desktop.
SELECT
p.uuid p_uuid,
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,
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,
cp.uuid cp_uuid,
cp.id cp_id,
cp.party_type cp_party_type,
cp.name cp_name,
cp.person_identifier cp_person_identifier,
cp.organization_identifier cp_organization_identifier,
cp.created cp_created,
cp.updated cp_updated,
cp.unit_status cp_unit_status,
cp.unit_type cp_unit_type,
cp.telephone_number cp_telephone_number,
cp.mobile_number cp_mobile_number,
cp.fax_number cp_fax_number,
cp.email_address cp_email_address,
cp.internet_address cp_internet_address,
cp.mailing_address cp_org_mailing_address,
cp.business_address cp_business_address
FROM register.party p
FULL JOIN register.organization o 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 = @partyUuid
ORDER BY p.uuid
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment