Thank you Ant, that is straightforward! We need that sometimes to counter check all things.
Regards, Tantely. On Wed, Feb 22, 2017 at 7:16 AM, Ant Snyman <[email protected]> wrote: > ....and if you also want the datasets that are NOT assigned to ANY > orgunits you could change the two JOINs to LEFT OUTER JOIN..... > > On 21 February 2017 at 20:31, Jim Grace <[email protected]> wrote: > >> 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 < >> [email protected]> 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 : [email protected] >>> 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/> >> >> _______________________________________________ >> Mailing list: https://launchpad.net/~dhis2-users >> Post to : [email protected] >> Unsubscribe : https://launchpad.net/~dhis2-users >> More help : https://help.launchpad.net/ListHelp >> >> > > > -- > > *Ant Snyman* > > *Cell: 0824910449* > > *Landline: 028 2713242* > > > Health Information Systems Program - SA > > *This message and any attachments are subject to a disclaimer published at > http://www.hisp.org/policies.html#comms_disclaimer > <http://www.hisp.org/policies.html#comms_disclaimer>. Please read the > disclaimer before opening any attachment or taking any other action in > terms of this electronic transmission. If you cannot access the > disclaimer, kindly send an email to [email protected] > <[email protected]> and a copy will be provided to you. By replying to > this e-mail or opening any attachment you agree to be bound by the > provisions of the disclaimer* > -- *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 : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
