dhis2-devs team mailing list archive

[Jim Grace <jim@xxxxxxxxx>]

If you want to show each pair of assigned orgUnit and dataSet, try
something like:

SELECT ou.name AS orgunit, ds.name AS dataset
FROM dataset ds
JOIN datasetsource dss ON dss.datasetid = ds.datasetid
JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid
ORDER BY ou.name, ds.name;

If you want only one row per orgUnit and a column that concatenates all
assigned dataset names, try (in Postgresql):

SELECT ou.name AS orgunit, string_agg(ds.name, ' | ' ORDER BY ds.name) AS
datasets
FROM dataset ds
JOIN datasetsource dss ON dss.datasetid = ds.datasetid
JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid
GROUP BY ou.name
ORDER BY ou.name;

Cheers,
Jim


On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely <
traminosoa@xxxxxxxxxx> wrote:

> Dear Community,
>
> I am trying to pull out the list of all orgUnits and the name of the
> questionnaire assigned to each of them via SQL View. In which table should
> I base the SQL query in order to have the list of dataSet assigned to an
> orgUnit?
>
> Regards,
>
> Tantely.
>
> *This message and its attachments are confidential and solely for the
> intended recipients. If received in error, please delete them and notify
> the sender via reply e-mail immediately.*
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Jim Grace
Core developer, DHIS 2
HISP US Inc.
http://www.dhis2.org <https://www.dhis2.org/>