PHP MYSQL QUERY FETCH FROM TWO TABLES

Question

Please I'm new to PHP/MYSQL. I have two tables. Table staff has (id, fullname) and Table attendance has (id,staff_id). I intend to make a query to fetch all staff staff who do not have their id as staff_id in Attendance table. Below is my PDO code:

$att = $con->prepare('SELECT member_id FROM attendance');
$att->execute();
while ($att_fetch = $att->fetch()) {
$absent = $con->prepare('SELECT * FROM members WHERE id != "'.$att_fetch['member_id'].'" ');
$absent->execute();
$absent_fetch = $absent->fetch();
echo '
  <tr>
   <td class="name" data-id="'.$absent_fetch['id'].'">'.ucwords($absent_fetch['fullname']).'</td>
  </tr> 
';
}

Surprisingly, this returns all staffs present in the Attendance table. Please help me out

Answer 1

I intend to make a query to fetch all staff who do not have their id as staff_id in attendance table.

You don't need two queries plus some PHP logic for this. You can get the result that you want in a single query, using not exists:

select s.*
from staff s
where not exists (select 1 from attendance a where a.staff_id = s.id)