如何仅打印不存在于另一个表中的表的值 - mySQL

Question

In my SQL database I have a table namend "orders". As you can see the people ordered "cat", "dog" and "frog":

id | name 
---------
1  | cat   
2  | dog  
3  | frog    

I can print my orders like this:

$pdo = Database::connect();
$sql = 'SELECT * FROM orders ORDER BY id DESC';

foreach ($pdo->query($sql) as $row) {
    echo($row['name'].' ');
}

and get the result: cat dog frog

Now I have another table with my inventory named "inventory":

id | name 
---------
1  | cat   
2  | dog  
3  | frog    
4  | duck  

I have a selectbox on my website, which shows my inventory

<select name="animals">

<?php 
$sql = 'SELECT * FROM inventory ORDER BY id DESC';

foreach ($pdo->query($sql) as $row) {
  echo('<option value="'.$row['name'].'">'.$row['name'].'</option>');
}                   
?>
</select>

What I want to do now is inside my select box show ONLY the inventory which is not in my orders table. That means in this case my select box should only have the option "duck".

How do I have to change my selectbox to achieve what I need.

Answer 1

Add subselect to your query:

SELECT * FROM inventory WHERE name NOT IN (SELECT name FROM orders) ORDER BY id DESC