# Elasticsearch Query with subquery

**URL:** <https://discuss.elastic.co/t/elasticsearch-query-with-subquery/316545>\
**Category:** Elasticsearch\
**Created:** [October 13, 2022, 1:57pm UTC](https://discuss.elastic.co/t/elasticsearch-query-with-subquery/316545 "2022-10-13T13:57:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![samy111](https://avatars.discourse-cdn.com/v4/letter/s/7ab992/32.png) [@samy111](https://discuss.elastic.co/u/samy111)\
**Post date:** [October 13, 2022, 1:57pm UTC](https://discuss.elastic.co/t/elasticsearch-query-with-subquery/316545/1 "2022-10-13T13:57:29Z")

</div>

I'm relatively new to elasticsearch. I can able to make simple query in dev tools. I need a help on converting the following sql into es query

```auto
select c.conversationid from conversations c
where c.conversationid not in
 (select s.conversationid from conversations s 
where s.type='end' and s.conversationid=c.conversationid)

```

Index looks like below.

| conversationid | type |
| --- | --- |
| 1 | start |
| 2 | start |
| 1 | end |
| 3 | start |

If I execute above query I will get the following results.  
conversationid  
2  
3

---

<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:** [November 10, 2022, 1:57pm UTC](https://discuss.elastic.co/t/elasticsearch-query-with-subquery/316545/2 "2022-11-10T13:57:35Z")

</div>

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