Join two datatables using jquery or any other client side scripts

Question

I followed this article http://www.c-sharpcorner.com/article/display-sharepoint-list-data-in-jquery-data-table/ to retrieve SharePoint list items into jquery data-table.

I modified my code a little to retrieve items from 2 separate lists and in 2 separate datatables.

this 2 lists has one-many relationship but without lookup column. There is a common column which keeps the relation between 2 butu its not lookup column.

I want to join this 2 lists using above code or any other client side technology. Javascript or jquery is preferred though

is it possible?

Thanks

Answer 1

Below can get you started...Assuming you have column which connects both the List, you list data is in Json... this is based on example at ref link

   var colors = {"COLORS":[ {"1": 'red'}, { "2": 'yellow'}, {"3":'orange'}]}

    var fruits = {"FRUITS":[ {"1":'apple'}, { "2": 'banana'}, {"3":'orange'}]}

    var newFruits = {"NEW_FRUITS": [] }

    if(colors.COLORS.length == fruits.FRUITS.length){
        var temp, first;
        $.each(fruits.FRUITS, function(i){
            for(first in this)break;
            temp = {};
            temp[first] = [];
            temp[first].push(this[first]);
            temp[first].push(colors.COLORS[i][first]);
            newFruits.NEW_FRUITS.push(temp);
        });
    }

OR Use AlaSQL

Alasql JavaScript SQL library does exactly what you need in one line:

<script src="alasql.min.js"></script>
 <script>
    var data = { COLORS: [[1,"red"],[2,"yellow"],[3,"orange"]],            
                 FRUITS: [[1,"apple"],[2,"banana"],[3,"orange"]]};

    data.NEW_FRUITS = alasql('SELECT MATRIX COLORS.[0], COLORS.[1], FRUITS.[1] AS [2] \
         FROM ? AS COLORS JOIN ? AS FRUITS ON COLORS.[0] = FRUITS.[0]',
         [data.COLORS, data.FRUITS]);
 </script>

You can play with this example in jsFiddle.

This is a SQL expression, where:

SELECT - select operator

MATRIX - modifier, whci converts resultset from array of objects to array of arrays

COLORS.[0] - first column of COLORS array, etc.

FRUITS.1 AS 2 - the second column of array FRUITS will be stored as third column in resulting recordset FROM ?

AS COLORS - data array from parameters named COLORS in SQL statement

JOIN ? ON ... - join

[data.COLORS, data.FRUITS] - parameters with data arrays