Tell us what you are trying to do?
I want to create a table structure with sort able columns,column filtering, Export to CSV and Excel,pagination.
I know toolset currently does not have these functionalities. I know wpdatatables has these functionalities.
So could you please help me formulate a SQL query that I would be used in wpdatatables to generate a table that would fetch data from five different post types.
The have 5 posts types have the following post relationships
1. Province post type
2. District post type(many districts can be linked to one Province)
3. Sector post type(many Sectors can be linked to one District)
4. Cell post type(many Cells can be linked to one Sector)
5. Village post type(many Villages can be linked to one Cell)
I want the table structure to have the following columns
1.post title(from province post type)
2. wpcf-province-name(meta-key for province post type)
3.post title(from district post type)
4. wpcf-district-name(meta-key for district post type)
5.post title(from sector post type)
6. wpcf-sector-name(meta-key for sector post type)
7.post title(from cell post type)
8. wpcf-cell-name(meta-key for cell post type)
9.post title(from village post type)
10. wpcf-village-name(meta-key for village post type)
It is straightforward enough to construct a SQL query to retrieve data from 5 different post types, grouped by post type, but if you also want to retrieve data about the connections between these different post types, which are stored in custom database tables, and meta data from each, then that becomes an extremely complex query and is outside the scope of what we can help with here in support I'm afraid.
If you were trying to construct a table with the kind of structure you describe (ignoring the requirements for exporting to CSV etc), then it wouldn't be using a single View (with a single underlying query), it would be using a series of nested Views (and nested individual queries).
If you use a tool such as Query Monitor and add some PHP to a page that uses the relationships API functions to retrieve some related post data, you'll be able to see the generated SQL queries and see how complex they are even for relatively simple requests. That would at least give you a starting point for working out the full query you are aiming for.
