SELECT app.name,dept_name from ServiceApps_PPE.Admin.ApplicationsAdmin app join ServiceApps_PPE.Admin.DepartmentsAdmin dept ON app.dept_id=dept.id where app.status=1 ;
Error: “msg”: “No index available for ANSI join term dept”
Anything is wrong on above query, kindly suggest best option for to join 2 , 3 collections.
CREATE INDEX ix1 ON ServiceApps_PPE.Admin.ApplicationsAdmin(dept_id);
CREATE INDEX ix2 ON ServiceApps_PPE.Admin.DepartmentsAdmin(id);
would be the minimum required to satisfy the JOIN, though the Index advisor would suggest creating on status,dept_id,name for ApplicationsAdmin, and id,dept_name (assuming the projection dept_name is an attribute of dept) for ServiceApps_PPE.Admin.DepartmentsAdmin.
(You can try adding the "advise " prefix to your statement and see what it suggests locally. Ref: ADVISE | Couchbase Docs )