Hi
I would like to share an SQL statement that would help you list all your orgunits (code, coordinates, facility name, district, region, etc.) It is tricky to query the same column “organisationunit.name” twice. Here is the query:
···
================================================
SELECT ou.code,
ou.name as facility,
ou.coordinates,
par.name as district,
ou.contactperson,
ou.phonenumber,
(SELECT part.name
FROM organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit part ON ou.parentid = part.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE (ou.name is not null) AND (ou.name=par.name) AND (ous.level=3)
) AS region
FROM organisationunit ou
INNER JOIN _orgunitstructure ous ON ou.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit par ON ou.parentid = par.organisationunitid
INNER JOIN orgunitlevel oul ON ous.level = oul.level
WHERE ou.name is not null AND ous.level=4
ORDER BY Region, par.name, ou.code
=====================================================