# Queries on left join on a single index in elasticsearch **URL:** \ **Category:** Elasticsearch\ **Created:** [December 31, 2018, 10:21am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496 "2018-12-31T10:21:13Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 31, 2018, 10:21am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/1 "2018-12-31T10:21:13Z")
Hello, Is it possible to use left join on single index. Ex; i have a table Sample, loaded into elasticsearch with index name as index\_sample. So, i had a query left join applied to same table Sample, can i frame queries in elasticsearch with left join for the index index\_sample as done in sql query. Waiting for your response. Thanks inadvance. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [December 31, 2018, 10:26am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/2 "2018-12-31T10:26:34Z")
Elasticsearch does not support joins at all. There are a few ways to represent relationships between documents, e.g. parent-child, but that is not to be confused with joins. If you try to model data in a relational way in Elasticsearch you are likely to run into problems. When you model data in Elasticsearch you generally need to leave the relational mind set behind and start denormalising data into documents instead. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 31, 2018, 10:44am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/3 "2018-12-31T10:44:28Z")
Hi @Christian_Dahlqvist, Thanks a lot for your response, do parent-child relationships means like primary key and foreign key data in database. can you please share some knowledge on parent child relationship and for which scenarios we create them. Thanks inadvance ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [December 31, 2018, 10:50am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/4 "2018-12-31T10:50:44Z")
It allows you to set up a relationship between types of documents within a single index, but is not equivalent of primary-foreign key. It is useful when you have a parent entity that is updated frequently and you do not want to update all the children. It does however come with a number of limitations and performance penalty. I would therefore recommend to instead model your data without relations to as great extent as possible. It might help us give better advice and examples if you could describe your data and what you are looking to achieve at a high level. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [December 31, 2018, 11:00am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/5 "2018-12-31T11:00:15Z")
Thanks for your response, I have a query as shown below in SQL, > select A.\*, revenue AS REV, month\_1 AS month, B.m\_branch, B.y\_brnach > from SAMPLE A > LEFT JOIN > ( > SELECT DISTINCT b\_no, SUM(current) AS m\_branch, SUM(c\_year) AS y\_brnach > FROM SAMPLE > WHERE id=@id > GROUP BY b\_no > ) AS B > ON A.b\_no=B.b\_no > WHERE id=@id > ORDER BY name, b\_no, c\_year DESC I want to show the equivalent in elasticsearch query. As informed by you, there is no possibility for joins in Elasticsearch. But can i split the data in parent-child and achieve this scenario. Waiting for your response. Thanks inadvance. ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [December 31, 2018, 11:30am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/6 "2018-12-31T11:30:19Z")
I am not sure I understand exactly what that does. It might be easier if you could describe the data and what you want to do at a higher level and not based on your current relational data model. ---
**Author:** ![balumurari1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/balumurari1/32/39203_2.png) [@balumurari1](https://discuss.elastic.co/u/balumurari1)\ **Post date:** [January 2, 2019, 7:59am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/7 "2019-01-02T07:59:52Z")
hi christian, Can you please give some sample examples to understand indetail about **parent-child relationships** , so that i will acquire some knowledge that in which scenarios can i use them. After going through this concept in internet, observed like With Elasticsearch 6.0, there are some fundamental changes which prevent parent/child relationships. 1. One index cannot contain more than one type. Read more [here](https://www.elastic.co/guide/en/elasticsearch/reference/6.x/removal-of-types.html). 2. Parent/child relationships have been removed, and hence the `_parent` field is also removed. You have to use join field instead of parent/child. Parent/child relationships required that there were two distinct types and both types were defined in the same index. Now that you can't have multiple types in one index, there is no way parent/child relationships can be supported in the same way that they were supported in 5.x and prior releases. You can refer to the join field documentation to see how to do similar things to parent/child relationships. But now, you have to define both kinds of documents within a single Elasticsearch index, within the same type. Please see the example which explains how to model "1 to many" kind of relationships (1 question, multiple answers related to that question) using a join field [here](https://www.elastic.co/guide/en/elasticsearch/reference/6.0/parent-join.html). for example, an institute will have different number of students. so institute data is parent, students data is child. How to give mappings for this relation(institute & student details) and load data from database. Waiting for your response, thanks inadvance, ---
**Author:** ![Christian\_Dahlqvist](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/christian_dahlqvist/32/4617_2.png) [@Christian\_Dahlqvist](https://discuss.elastic.co/u/Christian_Dahlqvist)\ **Post date:** [January 2, 2019, 10:05am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/8 "2019-01-02T10:05:06Z")
If you look at the page about the join field it states the following: > The join field shouldn’t be used like joins in a relation database. In Elasticsearch the key to good performance is to de-normalize your data into documents. Each join field, `has_child` or `has_parent` query adds a significant tax to your query performance. Parent-child relationships are as as I described earlier useful when you have a parent entity that is very large and/or frequently updated and you do not want to update a very large number of documents in a denormalised data model. In many cases there the parent is infrequently updated or modified, it is often better to denormalise and perform the extra work when the parent is updated than pay the performance penalty for every query. If you could describe your data and use-case rather than discuss in terms of simplistic examples it might be easier for others to provide better guidance. I personally rarely use parent-child relationships as they generally are suitable for a relatively limited number of scenarios, so am probably not the right person to help you with its use. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [January 30, 2019, 10:05am UTC](https://discuss.elastic.co/t/queries-on-left-join-on-a-single-index-in-elasticsearch/162496/9 "2019-01-30T10:05:16Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.