# Querying more than one template (JOIN)

**URL:** <https://discourse.openehr.org/t/querying-more-than-one-template-join/3751>\
**Category:** AQL\
**Tags:** aql\
**Created:** [27 March 2023 08:27 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751 "2023-03-27T08:27:48Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 08:27 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/1 "2023-03-27T08:27:48Z")

</div>

Probably this is an overkill or there is somebody out there who has faced the same issue.  
I am trying to perform a simple “join” (as per SQL terms) to count patients that are contained in the 3 different templates (different archetypes), but I cannot seem to get a response. I am not sure of the behaviour hence seeking for help here, hopefully someone can guide me a.

However, if I try the count on individual templates, I do get the desired result.

I am using Better openEHR Platform.

```auto
SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.progress_note.v0]
CONTAINS COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1]
WHERE d/name/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'
OFFSET 0 LIMIT 5

```

Thank you.

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [27 March 2023 08:46 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/2 "2023-03-27T08:46:09Z")

</div>

```auto
SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
AND CONTAINS COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
AND CONTAINS COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1]
WHERE d/name/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'

```

Try adding the ‘and’ clauses as the current AQL implies Nesting of contains which is actually impossible.

Having said that, I’ m not sure you really need to be ‘joining’ this way - what is the actual question you are trying to answer?

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 08:58 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/3 "2023-03-27T08:58:25Z")

</div>

Thanks @ian.mcnicoll .

Well, my actual question/request is how can I count the number of subjects (Patient\_numbers) that have compositions created based on 3 different templates. I have 3 templates; demographics (Person), and the other 2 (Reports).

Possible that I have (Template 1)350 Subjects; but (Template 2) 40 progress\_notes and (Template 3) 30 reports.

So to get the right count numbers for the scenario described above, I need to do something related to “nesting” or joining I am assuming.

I hope this clarifies my question. Will be glad for any help or guidance.

Thanks,

Michael

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [27 March 2023 09:00 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/4 "2023-03-27T09:00:55Z")

</div>

You could also try

```auto
CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
AND COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])

```

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 09:21 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/5 "2023-03-27T09:21:23Z")

</div>

Unfortunately this results into “Query is not valid”

```auto
SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
 AND COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
 AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])
WHERE d/archetype_details/template_id/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'

```

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 10:17 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/6 "2023-03-27T10:17:10Z")

</div>

I tried to “simplify” the query with just two templates and using ‘value IN’ but still the nesting is not working out. probably my syntax is wrong. Will be glad for any suggestions.

`SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers FROM EHR e CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0] WHERE e/ehr_status/subject/external_ref/id/value IN (SELECT (DISTINCT e/ehr_status/subject/external_ref/id/value) FROM EHR e CONTAINS COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0] WHERE c/archetype_details/template_id/value='Follow-up') AND d/archetype_details/template_id/value='Person'`

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [27 March 2023 11:31 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/7 "2023-03-27T11:31:26Z")

</div>

> [@ian.mcnicoll](#):
>
> `WHERE d/name/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'`

This is working for me on BetterCDR

```auto
SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers

FROM EHR e

CONTAINS (COMPOSITION a[openEHR-EHR-COMPOSITION.adverse_reaction_list.v1] and COMPOSITION m[openEHR-EHR-COMPOSITION.medication_list.v0])

WHERE a/name/value='Adverse reaction list' and m/name/value='Medication statement list'

```

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 12:08 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/8 "2023-03-27T12:08:04Z")

</div>

> [@CzarMich](#):
>
> `c/archetype_details/template_id/value='Follow-up'`

Yes, you are right. With 2 Templates it works. So for now I will just stick to querying with the two templates.  
Thanks for the help.

```auto
` SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers

FROM EHR e

CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0] AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])

WHERE d/archetype_details/template_id/value='Person' AND f/archetype_details/template_id/value='Echokardiographie'`

```

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [27 March 2023 12:21 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/9 "2023-03-27T12:21:26Z")

</div>

I’d expect it to work with more than 2 templates

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 14:24 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/10 "2023-03-27T14:24:20Z")

</div>

Yes, it does. One of my template just had issues.

Thanks a bunch @ian.mcnicoll

---

<div class="post-metadata">

**Author:** ![ian.mcnicoll](https://discourse.openehr.org/user_avatar/discourse.openehr.org/ian.mcnicoll/32/4430_2.png) [@ian.mcnicoll](https://discourse.openehr.org/u/ian.mcnicoll)\
**Post date:** [27 March 2023 14:33 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/11 "2023-03-27T14:33:04Z")

</div>

> [@CzarMich](#):
>
> One of my template just had issues.

Welcome to my world!! Glad you got it working!!

---

<div class="post-metadata">

**Author:** ![pablo](https://discourse.openehr.org/user_avatar/discourse.openehr.org/pablo/32/3505_2.png) [@pablo](https://discourse.openehr.org/u/pablo)\
**Post date:** [27 March 2023 15:02 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/12 "2023-03-27T15:02:19Z")

</div>

> [@CzarMich](#):
>
> Yes, it does. One of my template just had issues.

Was that issue related with the template syntax or semantics? just curious.

---

<div class="post-metadata">

**Author:** ![CzarMich](https://discourse.openehr.org/user_avatar/discourse.openehr.org/czarmich/32/2996_2.png) [@CzarMich](https://discourse.openehr.org/u/CzarMich)\
**Post date:** [27 March 2023 16:30 UTC](https://discourse.openehr.org/t/querying-more-than-one-template-join/3751/13 "2023-03-27T16:30:51Z")

</div>

> [@CzarMich](#):
>
> `d/archetype_details/template_id/value='Person'`

Hi Pablo,  
It was a simple syntax issue in the naming.