dhis2-users team mailing list archive

[Ant Snyman <ant@xxxxxxxx>]

....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 <jim@xxxxxxxxx> 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 <
> 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/>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> 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 disclaimer@xxxxxxxx 
<disclaimer@xxxxxxxx> 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.*