How to fetch multiple table data with multiple queries to one data-table

Question

How can I fetch multiple table data with multiple queries to one jquery dataTable. I want to fetch multiple table data of multiple queries to one data-table. can i do it with one query according to my code, I tried MySQL UNION, Not suitable for me.

Fetch.php

$query1 = "SELECT SUM(score) AS marks, from_date FROM table1 WHERE stat = 0 GROUP BY name ";
$statement = $db->prepare($query1);
$statement->execute();
$output = array('data' => array());
$count = $statement->rowCount();
if($count > 0) {

while($row = $statement->fetch(PDO:: FETCH_OBJ)) {

    $score = $row->marks;
    $date = $row->from_date;
    $Team = "Team 1";

$output['data'][] = array( $score,$date,$Team);     
 } // /while 
}// if num_rows

$query2 = "SELECT SUM(marks) AS marks, from_date FROM table2 WHERE stat = 0 GROUP BY name ";
$statement = $db->prepare($query2);
$statement->execute();
$output = array('data' => array());
$count = $statement->rowCount();
if($count > 0) {

while($row = $statement->fetch(PDO:: FETCH_OBJ)) {

    $score = $row->marks;
    $date = $row->from_date;
    $Team = "Team 2";

$output['data'][] = array( $score,$date,$Team);     
}
}// if num_rows
echo json_encode($output);

html

<table id="Table">
    <thead>
      <tr>
        <th>Score</th>
        <th>Start Date</th>
        <th>Team</th>
      </tr>
    </thead>
    <tbody>

    </tbody>
</table>

<script>    
var score;
$(document).ready(function(){
score = $('#Table').DataTable({
    'ajax': 'fetch.php',
    'order': []
    });
});
</script>

Answer 1

You can use UNION ALL to fetch the result.

Note:- Both table column should be the same, else you will get an error.

$result = [];
try {
  $conn = new PDO('mysql:host=HOST;dbname=DB;charset=utf8mb4', 'USERNAME', 'PASSWORD');
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $conn->beginTransaction();

  $stmt1 = $conn->prepare("SELECT SUM(score) AS marks, from_date FROM table1 WHERE stat = 0 GROUP BY name UNION ALL SELECT SUM(marks) AS marks, from_date FROM table2 WHERE stat = 0 GROUP BY name ");
  $stmt1->execute();
  $result[]  = $stmt1->fetchAll(PDO::FETCH_ASSOC);
  $conn->commit();

} catch (PDOException $e) {
   echo $e->getMessage();
}   
echo json_encode($result);

For the team variable, you can try

SELECT SUM(score) AS marks, from_date,'Team1' AS team FROM table1 WHERE stat = 0 GROUP BY name 

and another query

SELECT SUM(marks) AS marks, from_date, 'Team2' AS team FROM table2 WHERE stat = 0 GROUP BY name