dhis2-devs team mailing list archive

[Juma Lungo <jlungo@xxxxxxxxx>]

Solved. 

The correct query is:
SELECT 
      org.code,
      org.name,
      org.contactperson, 
      org.coordinates,
      org.phonenumber,
      ogp.name as Type
 FROM 
      organisationunit org, 
      orgunitgroup ogp, 
      orgunitgroupmembers ogpm, 
      orgunitgroupsetmembers ogsm, 
      orgunitgroupset ogs  
WHERE
      (org.organisationunitid = ogpm.organisationunitid) AND
      (ogp.orgunitgroupid = ogpm.orgunitgroupid) AND
      (ogsm.orgunitgroupid = ogp.orgunitgroupid) AND
      (ogsm.orgunitgroupsetid = ogs.orgunitgroupsetid) AND
      (ogs.name='Type') AND
      (org.parentid = $P!{organisationunits})
ORDER BY org.name



On Sunday, August 17, 2014 11:34 AM, Juma Lungo <jlungo@xxxxxxxxx> wrote:
 


Hi,

I would like to create a report to list orgunit code, name, ... and 'ownership'. Which table stores the ownership standard list?

Here is the SQL:

SELECT organisationunit.code, organisationunit.name, organisationunit.coordinates, orgunitgroup.name as Type, organisationunit.contactperson, organisationunit.phonenumber FROM organisationunit INNER JOIN orgunitgroupmembers ON organisationunit.organisationunitid = orgunitgroupmembers.organisationunitid INNER JOIN orgunitgroup ON (orgunitgroup.orgunitgroupid = orgunitgroupmembers.orgunitgroupid) AND organisationunit.parentid = 101