....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 <j...@dhis2.org> 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 < > tramino...@mikolo.org> 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-d...@lists.launchpad.net >> 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 : dhis2-users@lists.launchpad.net > 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 disclai...@hisp.org <disclai...@hisp.org> 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.*
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp