Create DataTable from 2 SharePoint Online lists

Question

Can I join two SharePoint Lists and display in one DataTable?

I created my first dataTable but now trying to get data from another list. For example: I want the dataTable to display similar to a daily in/out board. The data for IN will come from the Staff Directory (columns: ID, StaffName, RegularHours), then OUT will come from the TimeOff list (columns: SDID, StaffName, StartDate, EndDate). Just trying to find another (without workflows) way to populate a Daily In/Out Board.

SAMPLE DATA (2 lists - 1 output): Currently has the following code based on LOVEZ response but it returns no value?

$(document).ready(function(){
loadData();
});


function loadData() { 
var siteUrl = _spPageContextInfo.webAbsoluteUrl;
var dataSDUrl = siteUrl + "/_api/web/lists/getbytitle('Staff Directory')/items?$filter=AccountStatus eq 'Active'&$select=ID, title, TodaySchedule";
var dataARUrl = siteUrl + "/_api/web/lists/getbytitle('AbsenceRequest')/items?$filter=((EventDate le '" + new Date().toISOString() + "') and (EndDate ge '" + new Date().toISOString() + "'))&$select=SDID, calDisplayFN, EventDate, EndDate, RequestType, Status, ActivityType";

var ajaxSD = $.ajax({
    url: dataSDUrl,
    type: "GET", 
    dataType: "json", 
    headers: { "accept": "application/json;odata=verbose" }
});

var ajaxAR = $.ajax({
    url: dataARUrl,
    type: "GET", 
    dataType: "json", 
    headers: { "accept": "application/json;odata=verbose" }
});


$.when(ajaxSD, ajaxAR).done(function(a1, a2) {

    var data = a1[0].d.results.concat(a2[0].d.results);

    $('#table_id').DataTable({
        srollY: 400, 
        "aaData": data,
        "aoColumns": [
            { "mData": "title" },
            { "mData": "TodaySchedule" }
        ]
    });
});
}

Answer 1

We can use $.when to execute callback when both Ajax calls were successful.

The code like this:

function LoadData()
{
   var call1 = $.ajax({
      url: _spPageContextInfo.webAbsoluteUrl+"/_api/web/lists/getbytitle('list1')/items?$select=Data1,Data2,Data3,Data4,Data5",
      type: "GET",
      dataType: "json",
      headers: {
         Accept: "application/json;odata=verbose"
      }       
   });

   var call2 = $.ajax({
      url: _spPageContextInfo.webAbsoluteUrl+"/_api/web/lists/getbytitle('list2')/items?$select=Data1,Data2,Data3,Data4,Data5",
      type: "GET",
      dataType: "json",
      headers: {
         Accept: "application/json;odata=verbose"
      }       
   });

   // When both Ajax requests were successful
   $.when(call1, call2).done(function(a1, a2){
      // a1 and a2 are arguments resolved for the call1 and call2 ajax requests, respectively.
      // Each argument is an array with the following structure: [ data, statusText, jqXHR ]

      // Merge data from two Ajax calls
      var data = a1[0].d.results.concat(a2[0].d.results);

      $('#example').dataTable({
         "aaData": data,
         "aoColumns": [
            { "mData": "Data1" },
            { "mData": "Data2" },
            { "mData": "Data3" },
            { "mData": "Data4" },
            { "mData": "Data5" }
         ]
      });
   });
}