# Creating a subquery - similar to SQL "not in or not exists"
**URL:** https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017
**Category:** Kibana
**Created:** [May 26, 2016, 5:43am UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017 "2016-05-26T05:43:41Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Lori](https://discuss.elastic.co/u/Lori)
#### Post date: [May 26, 2016, 5:43am UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/1 "2016-05-26T05:43:41Z")
Hello. I am using Kibana 4.5. I am trying to create a new custom filter based on a condition of another query. For example, values in query 1 -\> animal: cat dog pig horse rabbit bird Now, in query 2, I will select from my table containing 100+ animals but exclude values from query 1. How can I accomplish this and save as a filter or visualization so that I can apply to my dashboard?
Thanks,
Lori
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [May 26, 2016, 9:46pm UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/2 "2016-05-26T21:46:05Z")
You cannot do that in ES at all, so you cannot do it in KB.
---
### Author:  [@Lori](https://discuss.elastic.co/u/Lori)
#### Post date: [May 26, 2016, 10:02pm UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/3 "2016-05-26T22:02:49Z")
Bummer. Thank you for your response, Mark.
---
### Author:  [@Lori](https://discuss.elastic.co/u/Lori)
#### Post date: [May 27, 2016, 9:12pm UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/4 "2016-05-27T21:12:37Z")
Hi Mark,
One more question...
Is this possible in Kibana 4.5 - I am tracking customer transactions in stores. I want to count the store only if the number of transactions is \>1. Do you know of a way to accomplish this?
Thanks,
---
### Author:  [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [May 28, 2016, 12:38am UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/5 "2016-05-28T00:38:53Z")
Are you adding the transactions up with an aggregation?
---
### Author:  [@Lori](https://discuss.elastic.co/u/Lori)
#### Post date: [May 31, 2016, 6:35pm UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/6 "2016-05-31T18:35:19Z")
I selected distinct count and chose (terms=store) in the visualization. I have a job that runs every 30 minutes that adds new data to today's index (health-%{+YYYY.MM.dd}). The Discover panel contains all of the records accumulated thus far. The only thing that keeps them from being classified as duplicates is the timestamp. I want to count 1 record for store 00101. However, it counts all lines, so instead of the count =1, it returns 4. Also, if 3 lines = 0 and 1 line = a number \>0, then I do not want to include this store in my total counts. For 100,000+ records, it returns 9671 distinct stores when it should return 83. Any ideas?
May 27th 2016, 12:31:34.3434 00101 - US 0 - -
May 27th 2016, 12:34:55.5555 00101 - US 0 - -
May 27th 2016, 12:35:29.2929 00101 - US 0 - -
May 27th 2016, 12:52:28.2828 00101 - US 0 - -
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 6, 2017, 1:51pm UTC](https://discuss.elastic.co/t/creating-a-subquery-similar-to-sql-not-in-or-not-exists/51017/7 "2017-07-06T13:51:55Z")