....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.*
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
