# Combining two queries in one

What I want is getting all names (first, middle, last) of the users along with each of their total hours of work which will be calculated by the SQL statement: CAST(SUM(timediff(dateTimeOut,dateTimeIn) as time).

I was able to achieve this with two queries, but somehow, I feel that this is inefficient (?):

$sql = "SELECT id, firstName, middleName, lastName FROM users_info";$result = $pdo->query($sql);

$rows = array(); while($row = $result->fetch()){$time = $pdo->query( "SELECT CAST(SUM(timediff(dateTimeOut, dateTimeIn)) as time) as totalHoursWorked FROM users_time WHERE user_id = {$row['id']}"
)->fetch();

$row['totalHoursWorked'] =$time['totalHoursWorked'];
$rows[] =$row;

}

echo json_encode($rows);  I'll use the json data for a DataTable. users_info table +----+-----------+------------+----------+ | id | firstName | middleName | lastName | +----+-----------+------------+----------+ | 1 | Isabelle | Luna | Ibarra | | 2 | George | Boston | Everett | | 3 | Skyler | Land | Cohen | +----+-----------+------------+----------+ users_time table: +---------+---------+---------------------+--------------------+ | time_id | user_id | dateTimeIn | dateTimeOut | +---------+---------+---------------------+--------------------+ | 1 | 1 | 2019-06-14 00:00:00 | 2019-06-16 09:00:0 | | 2 | 1 | 2019-06-15 00:00:00 | 2019-06-16 08:00:0 | | 3 | 2 | 2019-06-14 00:00:00 | 2019-06-16 09:30:0 | | 4 | 2 | 2019-06-15 00:00:00 | 2019-06-16 08:30:0 | | 5 | 3 | 2019-06-16 00:00:00 | 2019-06-16 09:00:0 | +---------+---------+---------------------+--------------------+  db-fiddle link : https://www.db-fiddle.com/f/vpozKbVoXxPoGzcsBkLL36/0 • Could you provide us the DDL and insert statements? – dfhwze Jun 16 at 11:35 ## 2 Answers Assuming that the id's in users_info exist in users_time, INNER JOIN (or just JOIN) is the way to merge the tables. If id's in user_info might not exist in users_time then use LEFT JOIN to allow null values in totalHoursWorked. Assuming you want all four columns' data to be included in your echo'ed json string (your posted code isn't do that), you can just fetchAll() directly into your json_encode() and echo it -- all at once. SQL: (Demo) $sql = "SELECT i.id,
MAX(i.firstName),
MAX(i.middleName),
MAX(i.lastName),
CAST(SUM(TIMEDIFF(t.dateTimeOut, t.dateTimeIn)) AS TIME) AS totalHoursWorked
FROM users_info i
GROUP BY i.id";
$query =$pdo->query($sql); echo json_encode($query->fetchAll(PDO::FETCH_ASSOC));


If you are truly only returning a single column (totalHoursWorked) without any of the other data, then I don't see the need to involve the user_info table at all.

$sql = "SELECT id, firstName, middleName, lastName FROM users_info"; and "SELECT CAST(SUM(timediff(dateTimeOut, dateTimeIn)) as time) as totalHoursWorked FROM users_time WHERE user_id = {$row['id']}"


can be merged into a single query:

   SELECT r.id, r.totalHoursWorked, t.firstName, t.middleName, t.lastName
FROM (
SELECT q.id, SUM(q.hoursWorked) as totalHoursWorked
FROM (
SELECT
id
, timestampdiff(second, dateTimeIn, dateTimeOut) / 3600.0 as hoursWorked
FROM users_info
) q
GROUP BY id
) r

id  totalHoursWorked    firstName   middleName  lastName

• It's only returning a single row [{"id":40,"firstName":"Employee 406","middleName":"Generated","lastName":"Auto","totalHoursWorked":"225:00:00"}] instead of this: [{"id":40,"firstName":"Employee 406","middleName":"Generated","lastName":"Auto","totalHoursWorked":"09:00:00"},{"id":41,"firstName":"Employee 692","middleName":"Generated","lastName":"Auto","totalHoursWorked":"152:00:00"},{"id":42,"firstName":"Employee 399","middleName":"Generated","lastName":"Auto","totalHoursWorked":"64:00:00"}] Basically, it was adding all the rows in the table. – Gene Adrian San Luis Jun 16 at 11:30