title: Getting user info using JOINs in Cosmos DB
body: <p>The following is my <code>Company</code> object that I store in Cosmos DB. I have all the essential information about employees in <code>Employees</code> property. I also have a <code>Departments</code> property that both defines departments as well as its members.</p>
  <pre><code>{
    &quot;id&quot;: &quot;company-a&quot;,
    &quot;name&quot;: &quot;Company A&quot;,
    &quot;employees&quot;: [
      {
        &quot;id&quot;: &quot;john-smith&quot;,
        &quot;name&quot;: &quot;John Smith&quot;,
        &quot;email&quot;: &quot;john.smith@example.com&quot;
      },
      {
        &quot;id&quot;: &quot;jane-doe&quot;,
        &quot;name&quot;: &quot;Jane Doe&quot;,
        &quot;email&quot;: &quot;jane.doe@example.com&quot;
      },
      {
        &quot;id&quot;: &quot;brian-green&quot;,
        &quot;name&quot;: &quot;Brian Green&quot;,
        &quot;email&quot;: &quot;brian.green@example.com&quot;
      }
    ],
    &quot;departments&quot;: [
      {
        &quot;id&quot;: &quot;it&quot;,
        &quot;name&quot;: &quot;IT Department&quot;,
        &quot;members&quot;: [
          {
            &quot;id&quot;: &quot;john-smith&quot;,
            &quot;name&quot;: &quot;John Smith&quot;,
            &quot;isDepartmentHead&quot;: true
          },
          {
            &quot;id&quot;: &quot;brian-green&quot;,
            &quot;name&quot;: &quot;Brian Green&quot;,
            &quot;isDepartmentHead&quot;: false
          }
        ]
      },
      {
        &quot;id&quot;: &quot;hr&quot;,
        &quot;name&quot;: &quot;HR Department&quot;,
        &quot;members&quot;: [
          {
            &quot;id&quot;: &quot;jane-doe&quot;,
            &quot;name&quot;: &quot;Jane Doe&quot;,
            &quot;isDepartmentHead&quot;: true
          }
        ]
      }
    ]
  }
  </code></pre>
  <p>I'm trying to return a list of a particular department, including the employee's email which will come from <code>employees</code> property.</p>
  <p>Here's what I did but this is including all employees in the output:</p>
  <pre><code>SELECT dm.id, dm.name, e.email, em.isDepartmentHead
  FROM Companies c
     JOIN d IN c.departments
     JOIN dm IN d.members
     JOIN e IN c.Employees
  WHERE c.id = &quot;company-a&quot; AND d.id = &quot;hr&quot;
  </code></pre>
  <p>The correct output would be:</p>
  <pre><code>[
     {
        &quot;id&quot;: &quot;jane-doe&quot;,
        &quot;name&quot;: &quot;Jane Doe&quot;,
        &quot;email&quot;: &quot;jane.doe@example.com&quot;,
        &quot;isDepartmentHead&quot;: true
     }
  ]
  </code></pre>
  <p>How do I form my SQL statement to get all members of a department AND include employees' email addresses?</p>