# SQL "where not exists" equivalent for Elastic **URL:** \ **Category:** Kibana\ **Created:** [March 5, 2019, 11:24am UTC](https://discuss.elastic.co/t/sql-where-not-exists-equivalent-for-elastic/170877 "2019-03-05T11:24:33Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![CraigMc](https://avatars.discourse-cdn.com/v4/letter/c/cdc98d/32.png) [@CraigMc](https://discuss.elastic.co/u/CraigMc)\ **Post date:** [March 5, 2019, 11:24am UTC](https://discuss.elastic.co/t/sql-where-not-exists-equivalent-for-elastic/170877/1 "2019-03-05T11:24:33Z")
Hello, I have a set of stages I am logging. A GUID is on each record to tie the stage together. Example: Guid Stage 1 Stage 1 1 Stage 2 1 Stage 3 1 Stage 4 1 Stage 5 2 Stage 1 2 Stage 2 2 Stage 3 2 Stage 4 2 Stage 5 I am looking to put a visual together on any GUID which has any missing stages. In SQL I would use something along the lines of: select \* from tbl as a where stage = 1 and not exists ( select \* from tbl as b where stage = 5 and a.guid = b.guid ) Any advice on how to achieve this in elastic/kibana? ---
**Author:** ![Nathan\_Reese](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nathan_reese/32/84829_2.png) [@Nathan\_Reese](https://discuss.elastic.co/u/Nathan_Reese)\ **Post date:** [March 5, 2019, 8:10pm UTC](https://discuss.elastic.co/t/sql-where-not-exists-equivalent-for-elastic/170877/2 "2019-03-05T20:10:51Z")
You can use the [exists](https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-exists-query.html) to filter for fields that do not have values defined for a field. You can use the "Add filter" UI to create a does not exist filter. ![15%20PM](https://us1.discourse-cdn.com/elastic/original/3X/3/a/3a969e75e4c64300f794321ef61e2856e6c07ad8.png) ---
**Author:** ![CraigMc](https://avatars.discourse-cdn.com/v4/letter/c/cdc98d/32.png) [@CraigMc](https://discuss.elastic.co/u/CraigMc)\ **Post date:** [March 10, 2019, 3:38pm UTC](https://discuss.elastic.co/t/sql-where-not-exists-equivalent-for-elastic/170877/3 "2019-03-10T15:38:00Z")
Hi Nathan, Thanks for replying to my query. For my example, there are no missing fields within each document. Each log has GUID and a Stage. Rather, I'm looking for a method of searching for documents where a corresponding document does not exist. So for example If I have 3 logs: GUID Stage 1 1 1 2 2 1 I'm looking for a way of reporting that GUID 2 does not have logs for all stages. Any advice you can help me with? Thanks, Craig ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 7, 2019, 3:38pm UTC](https://discuss.elastic.co/t/sql-where-not-exists-equivalent-for-elastic/170877/4 "2019-04-07T15:38:02Z")
Closed on April 7, 2019, 3:38pm UTC This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.