Php mysqli 选择表中有数据，而不是在不同的表中

Question

这是我的"all_workers"表和"absent_workers"表

worker_id  worker_name     |   id       name      
|
1        frank         |    1       neil
2        tom           |    2       ryan
3        neil          |    3       david
4        steve         |    4       steve
5        ryan          |    .       .
6        david         |    .       .
7        .             |

我现在要从表中选择工作all_workers工作人员，但我的代码不起作用。 这是我的代码：

<?php
include 'config.php';
$stmt = $conn->prepare('SELECT * FROM absent_workers');
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array()){
$absent_name = $row['name'];
}
$stmt->close();
?>
<?php
include 'config.php';
$stmt = $conn->prepare('SELECT * FROM all_workers WHERE worker_name NOT IN ?');
$stmt->bind_param('s', $absent_name); 
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array()){
$worker_name = $row['worker_name'];
}
$stmt->close();
?> 

Answer 1

您似乎想要从absent_workers中不存在的all_workers中获取names。可以使用单个查询执行此操作，而不是在应用程序代码中循环。

一种选择是not exists：

select al.*
from all_workers al
where not exists (select 1 from absent_workers ab where ab.name = al.name)

您还可以使用和反左联接：

select al.*
from all_workers al
left join absent_workers ab where ab.name = al.name
where ab.name is null