How I can join ODK aggregate core tables

Hello, everyone I just need to know how different ODK aggreate core tables can be join with each other.
My understanding is they can be join by _URI of core tables with _PARENT_AURI for coreN tables.
like the following.

SELECT * FROM databaseName.TABLE_CORE as cr
inner join databaseName.TABLE_CORE2 as cr2
on cr2._PARENT_AURI=cr._URI

Can anyone please confirm this?
Also what is the usage of _TOP_LEVEL_AURI in coreN table?

Advance thanks for your help and explanation regarding this.

You might find this useful (, Although it might not answer your question completely, still it will give you enough info about the db structure.



I have been working on a customized dashboard on top of Aggregate, so I can tell you that the original queries differ, based on what you want to do. For example, I need to pull the database name of every form, which I get like this:

SELECT PERSIST_AS_TABLE_NAME from odk_prod._form_data_model where ELEMENT_NAME = 'meta' and URI_SUBMISSION_DATA_MODEL = (SELECT URI_SUBMISSION_DATA_MODEL from odk_prod._form_info_submission_association where URI_MD5_FORM_ID = (SELECT _TOP_LEVEL_AURI FROM odk_prod._form_info_fileset where FORM_NAME = '".$row['FORM_NAME']."'))

I hope this helps.

1 Like