JSOM - CAML query - Not able to get columns from joint lists

Question

I am trying to get data from 3 lists through Join. However not able to access columns of 2nd and 3rd list which i had fetched in query.

---

Relation between lists is as follows;

Test3rdList has Lookup column named RefCol which points to BLL list ID column.

Further list BLL has a Loolup column named RefField which points to LstChangeType list ID column

---

I want to fetch below columns from respective lists;

(Test3rdList) -> ID,Title

(BLL) -> ID

(LstChangeType) -> ID

---

Below is my code;

function retrieveListItems() {
    var clientContext = new SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('Test3rdList');

    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml(
            '<Query />' +
            '<ViewFields>'+                
            '<FieldRef Name=\'ID\' />' +
            '<FieldRef Name=\'Title\' />' +
            '<FieldRef Name=\'BLL_ID\' />' +                
            '<FieldRef Name=\'LstChangeType_ID\' />' +
            '</ViewFields>' +
            '<Joins>' +
                '<Join Type=\'INNER\' ListAlias=\'BLLs\'>' +
                    '<Eq>' +
                    '<FieldRef Name=\'RefCol\' RefType=\'ID\' />' +
                    '<FieldRef List=\'BLL\' Name=\'ID\' />' +
                    '</Eq>' +
                '</Join>' +
                '<Join Type=\'INNER\' ListAlias=\'LstChangeTypes\'>' +
                    '<Eq>' +
                    '<FieldRef Name=\'RefField\' RefType=\'ID\' />' +
                    '<FieldRef List=\'LstChangeTypes\' Name=\'ID\' />' +
                    '</Eq>' +
                '</Join>' +
            '</Joins>' +
            '<ProjectedFields>' +
                '<Field ShowField=\'ID\' Type=\'Lookup\' Name=\'BLL_ID\' List=\'BLLs\' />' +
                '<Field ShowField=\'ID\' Type=\'Lookup\' Name=\'LstChangeType_ID\' List=\'LstChangeTypes\' />' +
            '</ProjectedFields>' +
            '<QueryOptions />'
    );
    this.collListItem = oList.getItems(camlQuery);

    clientContext.load(this.collListItem);
    clientContext.executeQueryAsync(
        Function.createDelegate(this, this.onQuerySucceeded),
        Function.createDelegate(this, this.onQueryFailed)
    );
}

function onQuerySucceeded(sender, args) {
    var listItemInfo = '';
    var listItemEnumerator = collListItem.getEnumerator();

    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        listItemInfo += '\nID: ' + oListItem.get_id() +
            '\nTitle: ' + oListItem.get_item('Title') +
            '\nRefCol: ' + oListItem.get_item('RefCol').$1T_1;
        var tst = 1;
    }

    alert(listItemInfo.toString());
}

function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() +
        '\n' + args.get_stackTrace());
}

//$(document).ready(function(){
//SP.SOD.executeFunc('sp.js', 'SP.ClientContext', retrieveListItems);
//});

$(document).ready(function () {
    ExecuteOrDelayUntilScriptLoaded(retrieveListItems, "sp.js");
});

Answer 1

Shouldn't it be RefField instead of RefCol?

            <Join Type=\'INNER\' ListAlias=\'BLLs\'>' +
                '<Eq>' +
                '<FieldRef Name=\'RefField\' RefType=\'ID\' />' +
                '<FieldRef List=\'BLL\' Name=\'ID\' />' +
                '</Eq>' +
            '</Join>