Join with tree buckets

What is the most efficient query syntax to perform the following multiple join operation

List of all patients who was at Beverley hospital on the week beginning of 2011-08-18

SELECT patients.FIRST_NAME,

patients.LAST_NAME,

encounters.PATIENT

FROM organizations

JOIN encounters ON organizations. Id = encounters. ORGANIZATION

WHERE organizations. NAME= “Beverley hospital” AND encounters. STARTDATE = “2011-08-18T01:28:03Z”

JOIN patients ON patients. Id = encounters. PATIENT

The buckets are

encounters

  • BASE_ENCOUNTER_COST (number)
  • CODE (number)
  • DESCRIPTION (string)
  • ENCOUNTERCLASS (string)
  • Id (string)
  • ORGANIZATION (string)
  • PATIENT
  • PAYER (string)
  • PAYER_COVERAGE (number)
  • PROVIDER (string)
  • REASONCODE (number)
  • REASONDESCRIPTION
  • STARTDATE (string)
  • STOP (string)
  • TOTAL_CLAIM_COST

Patients( Id , First ,LAST_NAME , GENDER)

Organization ( Id -NAME )

The syntax is right. Check out the following blog ANSI JOIN Support in N1QL | The Couchbase Blog
And create right indexes https://index-advisor.couchbase.com/indexadvisor/#1

CREATE INDEX oix1 ON organizations(NAME,Id);
CREATE INDEX eix1 ON encounters(ORGANIZATION, STARTDATE, PATIENT);
CREATE INDEX pix1 ON patients(Id, FIRST_NAME, LAST_NAME);

WITH orgIds AS (SELECT RAW o.Id
             FROM organizations AS o
             WHERE o.NAME = "Beverley hospital")
SELECT p.FIRST_NAME, p.LAST_NAME, e.PATIENT
FROM encounters AS e
JOIN patients AS p ON p.Id = e.PATIENT
WHERE e.STARTDATE = "2011-08-18T01:28:03Z" AND e.ORGANIZATION IN orgIds;
1 Like

thanks a lot
the first give me
“msg”: “syntax error - at JOIN”,

i tried another

SELECT patients.FIRST_NAME,

   patients.LAST_NAME,

   encounters.PATIENT

FROM patients

JOIN encounters ON patients. Id = encounters. PATIENT

AND encounters. STARTDATE = "1999-10-08T10:07:55Z"

JOIN organizations ON organizations. Id = encounters. ORGANIZATION

AND organizations. NAME= "BEVERLY HOSPITAL CORPORATION"

and it give me
execution: 34.9s

your query give me
elapsed: 13ms