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

Question

我环顾四周，似乎找不到以下问题的答案:

我想要做的就是运行一个 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>";

Answer 1

您可以使用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);

或左连接:

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;