# SQL Query with "NOT IN" keyword replacement in ES

**URL:** https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455
**Category:** Elasticsearch
**Created:** [November 28, 2018, 4:50am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455 "2018-11-28T04:50:06Z")
**Posts on this page:** 7
**Page:** 1

<div class="post-metadata">

### Author: ![W.anjum](https://avatars.discourse-cdn.com/v4/letter/w/a9a28c/32.png) [@W.anjum](https://discuss.elastic.co/u/W.anjum)
#### Post date: [November 28, 2018, 4:50am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/1 "2018-11-28T04:50:07Z")

</div>

I need to filter results from my indices based on this scenario:  
Select \* from TblName where status='ERROR' not in (select \* from TbleName where status='OK' )

I am unable to get desired results using must, must not etc.

---

<div class="post-metadata">

### Author: ![W.anjum](https://avatars.discourse-cdn.com/v4/letter/w/a9a28c/32.png) [@W.anjum](https://discuss.elastic.co/u/W.anjum)
#### Post date: [December 5, 2018, 5:48am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/2 "2018-12-05T05:48:16Z")

</div>

I guess nobody understood the question?

---

<div class="post-metadata">

### Author: ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)
#### Post date: [December 5, 2018, 7:15am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/3 "2018-12-05T07:15:50Z")

</div>

you must prepare query like

select \* FROM TblName where status NOT IN('OK','OK1')

---

<div class="post-metadata">

### Author: ![W.anjum](https://avatars.discourse-cdn.com/v4/letter/w/a9a28c/32.png) [@W.anjum](https://discuss.elastic.co/u/W.anjum)
#### Post date: [December 5, 2018, 10:11am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/4 "2018-12-05T10:11:04Z")

</div>

Sorry I didn't got your logic. Can you please elaborate a little. ☹

---

<div class="post-metadata">

### Author: ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)
#### Post date: [December 5, 2018, 10:20am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/5 "2018-12-05T10:20:38Z")

</div>

As far as I know, Elasticsearch SQL does not support sub-selects.

---

<div class="post-metadata">

### Author: ![W.anjum](https://avatars.discourse-cdn.com/v4/letter/w/a9a28c/32.png) [@W.anjum](https://discuss.elastic.co/u/W.anjum)
#### Post date: [December 5, 2018, 10:23am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/6 "2018-12-05T10:23:09Z")

</div>

It doesn't matter its SQL or elastic search query. I need a solution for this logic:  
[ES Logic](https://discuss.elastic.co/t/elastic-search-query-building-logic/156693?u=w.anjum)

---

<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: [January 28, 2019, 7:11am UTC](https://discuss.elastic.co/t/sql-query-with-not-in-keyword-replacement-in-es/158455/8 "2019-01-28T07:11:05Z")

</div>

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