How can I join data from two custom lists in SharePoint add-ins?

Question

I want join 2 custom lists in add-ins:

Data of Lists: QuanLyMembers.PhongBanId is ID value of QuanLyPhongBan

QuanLyPhongBan list:

QuanLyMembers list:

This is my code:

function getListMem() {
    //var hostWebContext = new SP.AppContextSite(clientContext, hostWebURL);
    var listStatus = clientContext.get_web().get_lists().getByTitle('QuanLyMembers');
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml("<View><ViewFields><FieldRef Name='Id'/><FieldRef Name='Title'/>" +
        "<FieldRef Name='EmailContact'/><FieldRef Name='PhongBanName'/></ViewFields>" +
        "<Joins><Join Type ='INNER' ListAlias ='QuanLyPhongBan'><Eq><FieldRef Name='PhongBanId' RefType='Id' /><FieldRef List='QuanLyPhongBan' Name='ID' /></Eq>" +
        "<ProjectedFields><Field Name='PhongBanName' Type='Lookup' List='QuanLyPhongBan' ShowField='Title'/> </ProjectedFields></Join>" +
        "</Joins></View>");
    var Items = listStatus.getItems(camlQuery);
    clientContext.load(Items, 'Include(Title,EmailContact,Id,PhongBanName)');
    clientContext.executeQueryAsync(Function.createDelegate(this, function () {
      
        var oEnumerator = Items.getEnumerator();    
        while (oEnumerator.moveNext()) {           
            var id = oEnumerator.get_current().get_id();
            var title = oEnumerator.get_current().get_item('Title');
            var PhongBanName = oEnumerator.get_current().get_item('PhongBanName ');
            var EmailContact = oEnumerator.get_current().get_item('EmailContact');
           

        }
       

    }), Function.createDelegate(this, function (sender, args) {
        alert('request failed ' + args.get_message() + '\n' + args.get_stackTrace());

    }));
}

But it throws an error:

"Value does not fall within the expected range."

How can I join data from two custom lists in SharePoint add-ins?

Answer 1

I think you have to create a lookup column for this.

Create a lookup column in QuanLyMembers list referencing to QuanLyPhongBan list.

Check below note from Microsoft:

There are requirements to keep in mind when creating list joins. You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the "join on" pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists.

Source: List Joins and Projections