Join query not working in scopes

Hi ,

I have tried following query in new scope. Managed to create 2 index’s on both a collection, but its not working.

Bucket: ServiceApps_PPE
Scope: Admin
Collection: DepartmentsAdmin & ApplicationsAdmin

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.

Thanks,

What are your index definitions?

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 )

HTH.

1 Like

Also checkout ANSI JOIN Support in N1QL - The Couchbase Blog
https://index-advisor.couchbase.com/indexadvisor/#1

1 Like

Thanks @dh & @vsr1 , i missed to add dept_id index.