SQL Test Queries JOIN

These are the tests and expected results for JOINs. These tests can be found in camlsql.sql_joins.spec.js.

SQL Test Query - JOIN #001

Join the linked list with the alias IssueList via the Lookup column "Issues"

1. "SELECT * FROM Comments LEFT JOIN IssueList ON Comments.Issue", []

<View>
    <Joins>
        <Join ListAlias="IssueList">
            <Eq>
                <FieldRef Name="Issue" RefType="Id" />
                <FieldRef List="IssueList" Name="Id" />
            </Eq>
        </Join>
    </Joins>
</View>

SQL Test Query - JOIN #002

Expected error since the IssueList.Created column was not explicitly included in the field list

1. "SELECT * FROM Comments LEFT JOIN IssueList ON comments.Issue WHERE issuelist.Created = ?", [camlsql.today()];

Uncaught [camlsql] The projected field 'IssueList.Created' must be explicitly included in the query

• See JOIN #003 for how to do it properly

SQL Test Query - JOIN #003

Join list on linked field and define a WHERE condition on a field in the joined list

1. "SELECT Title, IssueList.Created AS IssueCreated FROM Comments LEFT JOIN IssueList ON Comments.Issue WHERE IssueCreated = ?", [camlsql.today()]

<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name="IssueCreated" />
                <Value Type="DateTime">
                    <Today />
                </Value>
            </Eq>
        </Where>
    </Query>
    <Joins>
        <Join ListAlias="IssueList">
            <Eq>
                <FieldRef Name="Issue" RefType="Id" />
                <FieldRef List="IssueList" Name="Id" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field Name="IssueCreated" List="IssueList" Type="Lookup" ShowField="Created" />
    </ProjectedFields>
    <ViewFields>
        <FieldRef Name="Title" />
        <FieldRef Name="IssueCreated" />
    </ViewFields>
</View>

SQL Test Query - JOIN #004

With specified fields you can query on Projected fields without including it in the fields list. Then one will be created automatically.

1. "SELECT Title FROM Comments LEFT JOIN IssueList ON Comments.Issue WHERE IssueList.Created = ?", [camlsql.today()]

<View>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name="camlsqlfld_1" />
                <Value Type="DateTime">
                    <Today />
                </Value>
            </Eq>
        </Where>
    </Query>
    <Joins>
        <Join ListAlias="IssueList">
            <Eq>
                <FieldRef Name="Issue" RefType="Id" />
                <FieldRef List="IssueList" Name="Id" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field Name="camlsqlfld_1" List="IssueList" Type="Lookup" ShowField="Created" />
    </ProjectedFields>
    <ViewFields>
        <FieldRef Name="Title" />
        <FieldRef Name="camlsqlfld_1" />
    </ViewFields>
</View>

SQL Test Query - JOIN #005

Fail if you have a special character (dot in this case) in the list alias and have no surrounding brackets

1. "SELECT Title, IssueList.Created AS IssueCreated FROM Comments LEFT JOIN IssueList.Hej ON Comments.Issue WHERE IssueCreated = ?", [camlsql.today()]

[camlsql] Wrap list alias in brackets if it contains special characters: IssueList.Hej

SQL Test Query - JOIN #006

For comments, join connected issue from separate list. Then join the Priority of that issue from yet another list.

1. "SELECT Title, IssueList.Created AS IssueCreated FROM Comments LEFT JOIN IssueList ON Comments.Issue JOIN IssuePriorityList ON IssueList.Prio WHERE IssueCreated = ?", [camlsql.today()]

    <Query>
        <Where>
            <Eq>
                <FieldRef Name="IssueCreated" />
                <Value Type="DateTime">
                    <Today />
                </Value>
            </Eq>
        </Where>
    </Query>
    <Joins>
        <Join ListAlias="IssueList">
            <Eq>
                <FieldRef Name="Issue" RefType="Id" />
                <FieldRef List="IssueList" Name="Id" />
            </Eq>
        </Join>
        <Join ListAlias="IssuePriorityList">
            <Eq>
                <FieldRef List="IssueList" Name="Prio" RefType="Id" />
                <FieldRef List="IssuePriorityList" Name="Id" />
            </Eq>
        </Join>
    </Joins>
    <ProjectedFields>
        <Field Name="IssueCreated" List="IssueList" Type="Lookup" ShowField="Created" />
    </ProjectedFields>
    <ViewFields>
        <FieldRef Name="Title" />
        <FieldRef Name="IssueCreated" />
    </ViewFields>
</View>