# Is it possible to do subqueries?
**URL:** https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006
**Category:** Elasticsearch
**Created:** [September 10, 2015, 9:32am UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006 "2015-09-10T09:32:42Z")
**Posts on this page:** 5
**Page:** 1
### Author:  [@Mark\_Wade](https://discuss.elastic.co/u/Mark_Wade)
#### Post date: [September 10, 2015, 9:32am UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006/1 "2015-09-10T09:32:42Z")
I will have an index containing messages, sort of like this:
```
+---------------------------------+
| thread_id | author_id | message |
+-----------+-----------+---------+
| 1 | 1 | Lorem.. |
+-----------+-----------+---------+
| 1 | 2 | Lorem.. |
+-----------+-----------+---------+
| 1 | 3 | Lorem.. |
+-----------+-----------+---------+
| 2 | 2 | Lorem.. |
+-----------+-----------+---------+
| 2 | 3 | Lorem.. |
+-----------+-----------+---------+
| 3 | 1 | Lorem.. |
+---------------------------------+
```
I want to be able to get all messages from threads a particular user has participated in. In SQL I am doing this with this query:
```
SELECT * FROM my_index WHERE thread_id IN ( SELECT DISTINCT thread_id FROM my_index WHERE author_id = 1 )
```
Is there a way to do something like this with Elastic?
---
### Author:  [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)
#### Post date: [September 10, 2015, 10:22am UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006/2 "2015-09-10T10:22:21Z")
> I want to be able to get all messages from threads a particular user has participated in.
Given your example table and query is the solution not the equivalent of:
```
SELECT * FROM my_index WHERE author_id = 1
```
? We can do this but if you want the list of unique thread IDs for a user we can do that too.
Need to understand your problem better.
---
### Author:  [@Mark\_Wade](https://discuss.elastic.co/u/Mark_Wade)
#### Post date: [September 10, 2015, 10:39am UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006/3 "2015-09-10T10:39:24Z")
Not quite, that would get all messages a user posted, but I want not only their own posts, but all posts from any thread they have participated in.
So to use this thread as an example, I want a query that would return not only my 2 posts, but your post too.
It's so they then get sorted by newest first to display a sort of "updates to conversations you've participated in" page.
---
### Author:  [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)
#### Post date: [September 10, 2015, 10:52am UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006/4 "2015-09-10T10:52:02Z")
My bad. Coffee obviously required here.
You could use a parent/child relationship to associate individual post docs with thread docs.
The query would then find all parent (ie thread) docs that have a child (i.e. post) that belongs to your author. I't may help to add some additional filter criteria to this query though so that we only consider threads that have a child post which has been added in the last hour/day/week or whatever is reasonable.
This wouldn't necessarily give you threads that had been updated since the user's last post however (if he was the last to post in that thread).
Another option is to update a "thread summary" doc which contains fields for last\_updated, last\_author and all\_authors. You could then query for all threads where last\_updated \>dateX and last\_author!=userY and userY in all\_authors.
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [July 5, 2017, 11:51pm UTC](https://discuss.elastic.co/t/is-it-possible-to-do-subqueries/29006/5 "2017-07-05T23:51:17Z")