Issue in Advanced Sync Rules for JOIN Query

Elastic Stack Elasticsearch
1

Hi Elastic,

I have a question to ask where I've encountered issue with the Advanced Sync Rules Query :

Screenshot 2023-12-19 at 9.32.33 AM
Screenshot 2023-12-19 at 9.32.33 AM2204×1178 183 KB

So I have an application lets call it myStaff, where I've been pulling multiple tables from the db using Microsoft SQL Connectors. As I need to JOIN multiple tables, I face blocker in doing the JOIN query using the Advanced rules.

I notice from the docker logs that it keep skipping my custom query :

Screenshot 2023-12-18 at 10.09.27 AM
Screenshot 2023-12-18 at 10.09.27 AM3360×232 164 KB

As I construct any other rules than the JOIN rules, its working.
Even I tried the simplest JOIN couldn't works the query.
Below is one of the example query :

{
    "tables": [
      "Post",
      "Channel"
    ],
    "query": "SELECT TOP 100 P.id as PostID, C.id as ChannelID from Post P INNER JOIN Channel C ON C.Id = P.ChannelId where C.IsDeleted ='0' and C.isPrivate ='0' and P.IsDeleted ='0'"
  },

I've already checked with the technical team on the queries and everything works from their side. When they test the query. Is there any issue with the JOIN query?
I've been looking into this reference for the query and I'm already in 8.11.1 ver of Elastic :
https://www.elastic.co/guide/en/enterprise-search/8.11/connectors-ms-sql.html#connectors-ms-sql-sync-rules-example-one-join

Need help with this, thanks.

2

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