# Join style queries **URL:** \ **Category:** Elasticsearch\ **Created:** [July 26, 2012, 9:35am UTC](https://discuss.elastic.co/t/join-style-queries/8520 "2012-07-26T09:35:22Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![Schmurfy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/schmurfy/32/2763_2.png) [@Schmurfy](https://discuss.elastic.co/u/Schmurfy)\ **Post date:** [July 26, 2012, 9:35am UTC](https://discuss.elastic.co/t/join-style-queries/8520/1 "2012-07-26T09:35:22Z")
Hi, First what I am trying to achieve is I have a list of all the valid cities name and a list of contacts with a city field amongst others, I need to validate that every cities in the contact are either valid or run an approximate search to find the closest city matching the name in the contact. Now what I would do in a relational database is dump the cities in one table, the contacts in another and join them to find all the contacts with an invalid city but in such database I would not be able to run approximate searchs with tools like fuzzy queries provided by elasticsearch, so my question is can I do this in elasticsearch and how ? There is a lot of cities so including them in the query would result in a really big query and since I will need to do the same things for other attributes where the references include millions of values I hope there is another way xD From what I understand there is no way to really "join" two indices but how would you do that if you had to ? I have no constraints so I can change my schema as needed if it helps achieving my goal. Thanks for any help. ---
**Author:** ![mvg](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mvg/32/98890_2.png) [@mvg](https://discuss.elastic.co/u/mvg)\ **Post date:** [July 27, 2012, 10:34am UTC](https://discuss.elastic.co/t/join-style-queries/8520/2 "2012-07-27T10:34:19Z")
Hi Jullien, What you can do to implement this is to create to indices. One containing your contacts and one containing the city names. When you need to validate a contact's city name or you just to run an approximate city name search you can use the fuzzy like this query: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Just put the city name in your "like\_text" parameter and execute the query on the index that contains the city names. Warning: fuzzy queries in general are one of the more expensive queries to execute in the Lucene version ES uses at the moment (3.6). How expensive is difficult to say. Best way is just to try it out in your in your environment. Create an index that contains all unique city names and run some queries. The upcoming Lucene 4 can execute fuzzy queries much faster compared to Lucene 3.x. Martijn On 26 July 2012 11:35, Julien Ammous [schmurfy@gmail.com](mailto:schmurfy@gmail.com) wrote: > Hi, > First what I am trying to achieve is I have a list of all the valid cities > name and a list of contacts with a city field amongst others, I need to > validate that every cities in the > contact are either valid or run an approximate search to find the closest > city matching the name in the contact. > > Now what I would do in a relational database is dump the cities in one > table, the contacts in another and join them to find all the contacts with > an invalid city but in such database I would not be able to run approximate > searchs > with tools like fuzzy queries provided by elasticsearch, so my question is > can I do this in elasticsearch and how ? > There is a lot of cities so including them in the query would result in a > really big query and since I will need to do the same things for other > attributes where the references include millions of values I hope there is > another way xD > > From what I understand there is no way to really "join" two indices but how > would you do that if you had to ? > I have no constraints so I can change my schema as needed if it helps > achieving my goal. > > Thanks for any help. -- Met vriendelijke groet, Martijn van Groningen ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:18am UTC](https://discuss.elastic.co/t/join-style-queries/8520/3 "2017-07-06T03:18:48Z")
Closed on July 6, 2017, 3:18am UTC