php - 检查值不在查询的另一个表中

我环顾四周，但似乎找不到以下答案：

我要做的只是运行一条SQL select语句，以向用户显示提供发票编号（在这种情况下为唯一ID）的信息，而该信息未显示在同一数据库的另一个表中，此时代码如下：

$query = mysql_query("SELECT `ordate`, `invno` FROM `armast09` WHERE cshipno = '$username' AND `ordate` > DATE_SUB(CURDATE(), INTERVAL 4 DAY)") or die(header("location:index.php?e=4"));
$numrows = mysql_num_rows($query);

$date = date('o-n-d');

include 'indexfiles/top.php';

    echo "The following deliveries are available for viewing / dispute:";
    echo "<br />";
    echo "<br />";
    echo "<center>";
    echo "<table>";
        if($numrows != 0) {
            while ($info = mysql_fetch_array($query)) {
                echo "<tr>";
                    echo "<form action = \"deliverydispute.php?invno=".$info['invno']."\" method=\"POST\ onsubmit=\"return confirm('Are you sure you are ready to dispute? You can't go back after this.');\">";
                        echo "<td><b>Invoice Number: </b>".$info['invno']."</td>";
                        echo "<td><b>Order Date: </b>".$info['ordate']."</td>";
                        echo "<td><a href = \"deliverydispute.php?invno=".$info['invno']."&&ordate=".$info['ordate']."\">Dispute</a></td>";
                    echo "</form>";
                echo "</tr>";
            }
        } else {
            echo ("<font color=\"red\" face=\"arial\" size=\"2\"><small>No orders are currently available for viewing.</small></font>");
        }
    echo "</table>";

最佳答案

您可以使用NOT IN谓词：

SELECT ordate, invno
FROM armast09
WHERE cshipno = '$username'
  AND ordate > DATE_SUB(CURDATE(), INTERVAL 4 DAY)
  AND invno NOT IN(SELECT invno FROM Anothertable WHERE invno IS NOT NULL);

或LEFT JOIN：

SELECT a.ordate, a.invno
FROM armast09 a
LEFT JOIN anothertable a2 ON a.invno = a2.invno
WHERE a.cshipno = '$username'
  AND a.ordate > DATE_SUB(CURDATE(), INTERVAL 4 DAY)
  AND a.invno IS NULL;