# How to get data from Elasticsearch like SQL self join

**URL:** https://discuss.elastic.co/t/how-to-get-data-from-elasticsearch-like-sql-self-join/29480
**Category:** Elasticsearch
**Created:** [September 17, 2015, 1:21pm UTC](https://discuss.elastic.co/t/how-to-get-data-from-elasticsearch-like-sql-self-join/29480 "2015-09-17T13:21:49Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![mvu8912](https://avatars.discourse-cdn.com/v4/letter/m/edb3f5/32.png) [@mvu8912](https://discuss.elastic.co/u/mvu8912)
#### Post date: [September 17, 2015, 1:21pm UTC](https://discuss.elastic.co/t/how-to-get-data-from-elasticsearch-like-sql-self-join/29480/1 "2015-09-17T13:21:49Z")

</div>

I have a Elasticsearch query.

I can see the data this URL [http://localhost:9200/foobar/logs/\_search](http://localhost:9200/foobar/logs/_search)

But I want to get data doing a self join. I know how to do it in mysql.

But I am not sure how to do it in elasticsearch query.

Please advise.

```
USE foobar;
 SELECT From_unixtime(Truncate(Unix_timestamp(entry_date) / 300, 0) * 300) AS "every_5_mins",
        t2.transactionId,
        t1.eventName,
        COUNT(*)
 FROM logs t1
 LEFT JOIN foobar.logs t2
    ON t2.transactionId = t1.transactionId
   AND t2.eventType = "done"
 WHERE t1.eventName = "register"
 GROUP BY every_5_mins
```

---

<div class="post-metadata">

### Author: ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)
#### Post date: [September 19, 2015, 1:18am UTC](https://discuss.elastic.co/t/how-to-get-data-from-elasticsearch-like-sql-self-join/29480/2 "2015-09-19T01:18:17Z")

</div>

You can't do joins in nosql, which includes ES.  
You either need to add this data when indexing or look at nesting or parent/child.

---

<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: [July 5, 2017, 11:49pm UTC](https://discuss.elastic.co/t/how-to-get-data-from-elasticsearch-like-sql-self-join/29480/3 "2017-07-05T23:49:20Z")

</div>