# Joining more indexes and paginate the result

**URL:** <https://discuss.elastic.co/t/joining-more-indexes-and-paginate-the-result/224808>\
**Category:** Elasticsearch\
**Created:** [March 24, 2020, 10:43am UTC](https://discuss.elastic.co/t/joining-more-indexes-and-paginate-the-result/224808 "2020-03-24T10:43:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Matteo\_Depascale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/matteo_depascale/32/45680_2.png) [@Matteo\_Depascale](https://discuss.elastic.co/u/Matteo_Depascale)\
**Post date:** [March 24, 2020, 10:43am UTC](https://discuss.elastic.co/t/joining-more-indexes-and-paginate-the-result/224808/1 "2020-03-24T10:43:57Z")

</div>

**Describe the feature** :  
I'd like to be able to join more indexes and paginate the result

**Elasticsearch version** : 6.8.0

URL: POST ... .../\_opendistro/\_sql/

**Actual query:**  
`"query": "SELECT * FROM file f JOIN document d ON d.id = f.documentId WHERE d.documentCategoryId = '..." AND d.state = '...' LIMIT 5 "`  
this query is working fine, but it can skip the first x results

**Feature request**  
`"query": "SELECT * FROM file f JOIN document d ON d.id = f.documentId WHERE d.documentCategoryId = '..." AND d.state = '...' LIMIT 5 OFFSET 5"`

I don't actually need \_opendistro/\_sql/ if you have other options like:

- can I translate SQL query **with JOIN** in ES query?
- any way I can join 2 indexes and use `size` and `from`
- is there any option with `search_after` or `from`

---

<div class="post-metadata">

**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\
**Post date:** [March 24, 2020, 12:28pm UTC](https://discuss.elastic.co/t/joining-more-indexes-and-paginate-the-result/224808/2 "2020-03-24T12:28:27Z")

</div>

We don't support opendistro in this forum. You need to ask in their forums if you are not using the standard distribution.

Note that in elasticsearch we recommend doing "joins" at index time.  
If you want to do joins at search time, you should look at parent/child feature. May be that could work. Not sure if it's supported by this project though.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 21, 2020, 12:28pm UTC](https://discuss.elastic.co/t/joining-more-indexes-and-paginate-the-result/224808/3 "2020-04-21T12:28:35Z")

</div>

Closed on April 21, 2020, 12:28pm UTC

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.