# How can we convert join query into elastic search
**URL:** https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783
**Category:** Elasticsearch
**Created:** [September 3, 2019, 8:12am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783 "2019-09-03T08:12:15Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 3, 2019, 8:12am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/1 "2019-09-03T08:12:15Z")
Dear All.
i having problem with below query š
Please help me to convert into Elastic-search query:-
```
{
"query":"SELECT * FROM dbdataindex_join_allcolumns_qa INNER JOIN dbdataindex_join_allcolumns_qa_added_beneficiary_status ON dbdataindex_join_allcolumns_qa_added_beneficiary_status.beni_hrk= dbdataindex_join_allcolumns_qa.patient_to_care_hrk"
}
```
Thanks
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 3, 2019, 8:51am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/2 "2019-09-03T08:51:31Z")
You can not do joins with elasticsearch.
---
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 3, 2019, 9:12am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/3 "2019-09-03T09:12:50Z")
Hi @dadoonet
Thanks
but i am looking to convert same query as nested/parent\_child ?
because someone has given idea about that for implementation of join using nested or parent\_child but i don't have idea how can i implement with same.
Thanks
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 3, 2019, 10:43am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/4 "2019-09-03T10:43:24Z")
What is your current mapping? Are you using nested or parent/child?
Could you provide a full recreation script as described in [About the Elasticsearch category](https://discuss.elastic.co/t/about-the-elasticsearch-category/21). It will help to better understand what you are doing. Please, try to keep the example as simple as possible.
A full reproduction script will help readers to understand, reproduce and if needed fix your problem. It will also most likely help to get a faster answer.
---
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 3, 2019, 11:03am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/5 "2019-09-03T11:03:38Z")
Hi @dadoonet.
Thanks for quickly replied
Actually i am writing Query like below š and converting all sql query in DSL using translate(sql)
}
`{ "query":"SELECT * FROM dbdataindex_join_allcolumns_qa INNER JOIN dbdataindex_join_allcolumns_qa_added_beneficiary_status ON dbdataindex_join_allcolumns_qa_added_beneficiary_status.beni_hrk= dbdataindex_join_allcolumns_qa.patient_to_care_hrk" }`/\>
but when try to above query using same its show š
```
{
"error": {
"root_cause": [
{
"type": "parsing_exception",
"reason": "line 1:47: Queries with JOIN are not yet supported"
}
],
"type": "parsing_exception",
"reason": "line 1:47: Queries with JOIN are not yet supported"
},
"status": 400
```
but i want same query in DSL for more info i am not writing DSL Query in any case.
by sql query just converting it into DSL using TRANSLATE and passing with team.
Thanks
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 3, 2019, 11:30am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/6 "2019-09-03T11:30:56Z")
As the error message says, joins are not supported.
---
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 3, 2019, 11:50am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/7 "2019-09-03T11:50:28Z")
Hi. @dadoonet
Thanks
but i am looking for alternatives solution by using DSL Query.
we know very well its doesn't support Joins But is there any way of implement Join.
so we can work with current request.
Note:- i want DSL Query like below(but this is not join).
`< { "size" : 1000, "query" : { "bool" : { "must" : [{ "bool" : { "must" : [ { "bool" : { "must" : [ { "terms" : { "relation.keyword" : [ "P", "S"], "boost" : 1.0 } }, { "term" : { "user_id" : { "value" : 1016, "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "careprog_id" : { "value" : 7, "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, { "term" : { "active.keyword" : { "value" : "Y", "boost" : 1.0 } } } ], "adjust_pure_negative" : true, "boost" : 1.0 } }, "_source" : { "includes" : ["firstname", "lastname", "sex", "active", "relation"], "excludes" : [] }, "docvalue_fields" : [{ "field" : "dob", "format" : "epoch_millis" }, { "field" : "user_patient_hrk" }, { "field" : "user_id" }, { "field" : "careprog_id" }], "sort" : [{ "_doc" : { "order" : "asc" } }] } />`
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 3, 2019, 12:02pm UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/8 "2019-09-03T12:02:29Z")
Yes but this depends on your mapping as I asked:
> [@dadoonet](#):
>
> What is your current mapping? Are you using nested or parent/child?
---
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 5, 2019, 2:40pm UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/9 "2019-09-05T14:40:15Z")
Hi @dadoonet .
Thanks
but i don't have idea about implementation of parent\_child and nested query.
Note :- i have one more complex issue please review and please try to help?
> [@Elastic-search advanced search query issue](https://discuss.elastic.co/t/elastic-search-advanced-search-query-issue/198254):
>
> Dear All. I have some use case:- 01:- i have index(index01) that contains 8 fields :- fields like- a,b,c,d,e,f,g,h,i :- each fields has diff value 02:- now search condition here is? :- if i search for fields [a:"alpha"] then it will search all data matching with field[a] :- if search for fields [b:'data',c:'meta',d:'sata'] then it will search only fields[b,c,d] values but not other matched fields within [a,e,f,g,h] 03:- this is final case in, index fields[i] that contains integer vā¦
Thanks
---
### Author:  [@dadoonet](https://discuss.elastic.co/u/dadoonet)
#### Post date: [September 6, 2019, 9:48am UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/10 "2019-09-06T09:48:52Z")
Here are some links you could look at:
[https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html)
[https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/parent-join.html)
HTH
---
### Author:  [@rameshkr1994](https://discuss.elastic.co/u/rameshkr1994)
#### Post date: [September 6, 2019, 1:11pm UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/11 "2019-09-06T13:11:53Z")
Hi @dadoonet.
Thanks
but looking for like below use case i think this is very simple for you to resolve my use case issue.
> [@Elastic-search advanced search query issue](https://discuss.elastic.co/t/elastic-search-advanced-search-query-issue/198254/3):
>
> Hi @dadoonet. Thanks i am trying to search the records based on below sql query condition -1 condition :- 01 i have 8-fields in current index use case and i want to search all fields value based on provided value with fields like- field1:'data' field2:'sata' field5:2424 field8:PHM Condition02:- in above search field i am not providing field value for field3,4,6,7 but we need this field for next search in current query and want exact matching result with previous - field1:'data' fā¦
Thanks
HadoopHelp
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 4, 2019, 1:11pm UTC](https://discuss.elastic.co/t/how-can-we-convert-join-query-into-elastic-search/197783/12 "2019-10-04T13:11:54Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.