# Multiple SQL statements in query
**URL:** https://discuss.elastic.co/t/multiple-sql-statements-in-query/288494
**Category:** Kibana
**Tags:** elastic-stack-sql
**Created:** [November 5, 2021, 1:33pm UTC](https://discuss.elastic.co/t/multiple-sql-statements-in-query/288494 "2021-11-05T13:33:55Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@antongb](https://discuss.elastic.co/u/antongb)
#### Post date: [November 5, 2021, 1:33pm UTC](https://discuss.elastic.co/t/multiple-sql-statements-in-query/288494/1 "2021-11-05T13:33:55Z")
Hi,
I would like to design a query which finds the subset of field values based on the same values in a different document.
Here is the document structure for context:
```auto
TABLE = FRUIT_QUESTIONS
{"ID": 1, "question": "Do you like fruits?", "answer":"yes"}
{"ID": 1, "question": "Do you like vegetables?", "answer":"no"}
{"ID": 2, "question": "Do you like fruits?", "answer":"yes"}
{"ID": 2, "question": "Do you like vegetables?", "answer":"yes"}
```
In SQL this would look somethinglike this:
```auto
CREATE TABLE FIRST_QUERY AS SELECT ID FROM FRUIT_QUESTIONS WHERE answer='yes' AND question='Do you like fruits?';
SELECT count(*) FROM FRUIT_QUESTIONS WHERE answer='no' AND Q='Do you like vegetables?' AND ID in FIRST_QUERY ;
```
or all as one statement
```auto
SELECT count(*) FROM FRUIT_QUESTIONS WHERE A='yes' AND question='Do you like fruits?' AND id in (SELECT id FROM FRUIT_QUESTIONS WHERE answer='no' AND Q='Do you like vegetables?')
```
The query effectively is "how many people like fruid but don't like vegetables?" In this example the example is one person.
My problem is I'm not sure how to do this as a query in Kibana. Is there a way to translate this to query DSL?
Thanks
edit:
So elastic doesn't support `IN` but is there another way?
---
### Author:  [@tsullivan](https://discuss.elastic.co/u/tsullivan)
#### Post date: [December 2, 2021, 6:19pm UTC](https://discuss.elastic.co/t/multiple-sql-statements-in-query/288494/2 "2021-12-02T18:19:32Z")
The way to do this would be to start before adding the data to Elasticsearch. The documents need to decorated with fields that help searching for what you want:
```auto
{"ID": 2, "question": "Do you like vegetables?", "answer":"yes", "likes_veg":true,"likes_fruit":false}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [December 30, 2021, 6:20pm UTC](https://discuss.elastic.co/t/multiple-sql-statements-in-query/288494/3 "2021-12-30T18:20:16Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.