# Tracking any change to any of the multiple tables used in the join to create the index

**URL:** <https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007>\
**Category:** Logstash\
**Created:** [May 8, 2018, 12:57pm UTC](https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007 "2018-05-08T12:57:56Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![RRSR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rrsr/32/27766_2.png) [@RRSR](https://discuss.elastic.co/u/RRSR)\
**Post date:** [May 8, 2018, 12:57pm UTC](https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007/1 "2018-05-08T12:57:56Z")

</div>

Continuing the discussion from [JDBC and tracking\_column(s) questions](https://discuss.elastic.co/t/jdbc-and-tracking-column-s-questions/51997/2):

> [@JDBC and tracking\_column(s) questions](https://discuss.elastic.co/t/jdbc-and-tracking-column-s-questions/51997/2):
>
> Concatenate those two columns in your SELECT clause and use that as the tracking column.

I want to update the index whenever any of any of the tables used in the join to create the index is updated in MySQL. I'm using `jdbc` input plugin. So, if I concat all the related modified\_time, how can I compare those with the `sql_last_value` as that will be a string of dates?

Tagging - @Christian_Dahlqvist

---

<div class="post-metadata">

**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:** [May 9, 2018, 6:02am UTC](https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007/2 "2018-05-09T06:02:05Z")

</div>

I think this is mainly a SQL question, so am not sure I am qualified to answer. Could you perhaps create a view and have a column in the view that is the MAX of all modified dates included in the join? If that doesn't work, you could compare against multiple fields in the query.

---

<div class="post-metadata">

**Author:** ![RRSR](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rrsr/32/27766_2.png) [@RRSR](https://discuss.elastic.co/u/RRSR)\
**Post date:** [May 9, 2018, 9:52am UTC](https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007/3 "2018-05-09T09:52:41Z")

</div>

I used the `GREATEST()` function in my MySQL join query over all the `modified_time` fields so now if I change any data in any of the tables participating in `join` then that data gets indexed in the ElasticSearch.  
PS: I'm having triggers on all of my tables for creation & modification time 🙂

Thanks @Christian_Dahlqvist

---

<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 6, 2018, 9:52am UTC](https://discuss.elastic.co/t/tracking-any-change-to-any-of-the-multiple-tables-used-in-the-join-to-create-the-index/131007/4 "2018-06-06T09:52:43Z")

</div>

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