finding the number of different users missed tasks with join queries-CodePudding

I have two tables, one being the employees information and one with their task progress information. They look like this.

employee table                                  Task table
empid first_name last_name                      task_id    stats  pf  empid

1      lana        law                          1           0      1   6  
5      Carla        davis                       2           1      0   6   
6      lana         doe                         3           1      0   6
                                                4           0      1   5
                                                5           0      0   5
                                                6           1      0   5
                                                7           0      0   5
                                                8           0      1   6
                                                9           0      1   6

I want to produce a table that will show the number of missed tasks. Meaning the number of entries where pf = 1 and stats = 0. Using the above data it should look like this.

number     fname    lname
  0       lana      law
  1       Carla    davis
  3       lana      Doe

This is the code i have tried but it doesnt print correctly

$query1 = $conn-> query("SELECT empfname, emplname from employee");
      while ($row1 = $query1 -> fetch_array()){

      $query = $conn -> query("SELECT count(1) from tasks INNER JOIN employee where tasks.eemail = employee.eid and tasks.pf = '1' and tasks.stats = '0' ");  
      while($row = $query -> fetch_array())  
      {  

     $number = $row[0];     
     $output .= '<tr>  
                            
                          <td>'.$number.'</td>  
                          <td>'.$row1["empfname"].'</td>
                          <td>'.$row1["emplname"].'</td>
                 </tr>

This is the table i get when I run the above code which shows incorrect info

number     fname    lname
      4       lana      law
      4        Carla    davis
      4       lana      Doe

Could someone please help me figure out what ive done wrong with my query. Thanks in advance.

CodePudding user response：

You can fetch both by doing a JOIN. First we group by empid to count how many failed tasks. Then join THAT with the employee names.

SELECT `number`, fname, lname FROM 
(SELECT empid, COUNT(1) AS `number`
FROM `Task`
WHERE `stats` = 0 AND `pf` = 1
GROUP BY empid) AS a
LEFT JOIN `employee` AS b ON b.empid = a.empid

CodePudding user response：

This uses a sub query to count uncompleted tasks for each employee. Coalescing to zero if no matches found.

SELECT employee.fname, employee.lname,
COALESCE( (
  SELECT COUNT( Task.task_id )
  FROM Task
  WHERE Task.empid = employee.empid
  AND Task.stats = 0
  AND Task.pf = 1
), 0 ) AS uncompleted_tasks
FROM employee
WHERE 1