# Sub Query without Xpack or Nested mappings

**URL:** <https://discuss.elastic.co/t/sub-query-without-xpack-or-nested-mappings/273187>\
**Category:** Elasticsearch\
**Created:** [May 17, 2021, 2:49pm UTC](https://discuss.elastic.co/t/sub-query-without-xpack-or-nested-mappings/273187 "2021-05-17T14:49:51Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![bsmith](https://avatars.discourse-cdn.com/v4/letter/b/7feea3/32.png) [@bsmith](https://discuss.elastic.co/u/bsmith)\
**Post date:** [May 17, 2021, 2:49pm UTC](https://discuss.elastic.co/t/sub-query-without-xpack-or-nested-mappings/273187/1 "2021-05-17T14:49:51Z")

</div>

Trying to mimic the following SQL with the intent of using the results for Vega. SQL gets a count of employees who were active at the start date as well as the end date.

I have a feeling this can't be done without nested mappings, but I'm trying to avoid having to restructure the index around this.

```auto
select count(*)
from HEADCOUNT A
where DATE = '2021-01-02T00:00:00.000Z'
and STATUS = 'Active'
and EXISTS ( select 1
             from HEADCOUNT B
             where EMPLOYEE = B.EMPLOYEE
             and STATUS = 'Active'
             and DATE = '2021-03-31T00:00:00.000Z'

```

Below is an attempt at trying to put this together, but I know the headcount\_sub is incorrect

```auto
"Headcount": {
          "filter": {
            "query": {
              "bool": {
                "must": [
                  {
                    "match": {
                      "status": "active"
                    }
                  },
                  {
                    "match": {
                      "Time": "2021-01-02T00:00:00.000Z"
                    }
                  }
                ],
                "Headcount_SUB": {
                  "filter": {
                    "query": {
                      "bool": {
                        "must": [
                          {
                            "match": {
                              "status": "active"
                            }
                          },
                          {
                            "match": {
                              "Time": "2021-03-31T00:00:00.000Z"
                            }
                          }
                        ]
                      }
                    }
                  }
                },
                "minimum_should_match": 1
              }
            }
          },
          "aggs": {
            "headcount": {
              "value_count": {
                "field": "FIELD"
              }
            }
          }
        }

```

---

<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:** [June 14, 2021, 2:50pm UTC](https://discuss.elastic.co/t/sub-query-without-xpack-or-nested-mappings/273187/2 "2021-06-14T14:50:52Z")

</div>

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