# Elasticsearch: creating inner queries

**URL:** https://discuss.elastic.co/t/elasticsearch-creating-inner-queries/90093
**Category:** Elasticsearch
**Created:** [June 20, 2017, 12:15pm UTC](https://discuss.elastic.co/t/elasticsearch-creating-inner-queries/90093 "2017-06-20T12:15:08Z")
**Posts on this page:** 3
**Page:** 1

<div class="post-metadata">

### Author: ![dinaWork](https://avatars.discourse-cdn.com/v4/letter/d/edb3f5/32.png) [@dinaWork](https://discuss.elastic.co/u/dinaWork)
#### Post date: [June 20, 2017, 12:15pm UTC](https://discuss.elastic.co/t/elasticsearch-creating-inner-queries/90093/1 "2017-06-20T12:15:08Z")

</div>

here are my logs:

**index: purchase**

```
{details: { name: john, corID: 12345678 }}
{details: { name: bill, corID: 96657545}}

{town: NY, ID: 12345678 }
{a:b , v: g}
{a: hi, b: 12345678}
{g:f , k:ggg777 }

```

I would like to create a query which for a given name, the query will search in `purchase` index for `details.name=<name>` , extract `details.corID` and search in the indexe for logs which contain the `details.corID`

example for above details:

**name = "john"**

**query result:**  
(all logs which have `12345678`)

```
{details: { name: john, corID: 12345678 }}
{town: NY, ID: 12345678 }
{a: hi, b: 12345678}

```

this is how I would do it in SQL:

```
SELECT * FROM purchase
where corID= 
     (SELECT details.corID 
      FROM [purchase]
      where details.name = "john")
```

---

<div class="post-metadata">

### Author: ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)
#### Post date: [June 20, 2017, 2:27pm UTC](https://discuss.elastic.co/t/elasticsearch-creating-inner-queries/90093/2 "2017-06-20T14:27:17Z")

</div>

This is a join, and Elasticsearch does not support joins in general.

One workaround would be to use the parent/child feature in order to index the cor as a parent document and then use `has_child`/`has_parent` to join documents with their `corID`. But beware that parent/child joins come with a significant performance penalty.

---

<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 18, 2017, 2:27pm UTC](https://discuss.elastic.co/t/elasticsearch-creating-inner-queries/90093/3 "2017-07-18T14:27:36Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.