Join with tree buckets

Couchbase Server
, , ,
1

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 )

2

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
3

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