CODEWITHSUNDEEP
phpmysqlsqlselect
Henry Pye
Henry Pye

Reputation: 37

Checking a value isn't in another table in a query

I've looked around and I can't seem to find an answer to the following:

All I want to do is run an SQL select statement, to show the user something providing the invoice number (unique ID in this case) isn't showing in another table on the same database, at the moment the code is as follows: 

$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>";

Upvotes: 0

Views: 190

Answers (2)

Gats
Gats

Reputation: 3462

You can do this easiy with the following:

SELECT X FROM TABLEA LEFT JOIN TABLEB ON TABLEA.ID = TABLEB.ID WHERE TABLEB.ID is null

That should do it!

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79939

You can use the NOT IN predicate:

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);

Or 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;

Upvotes: 1

Related Questions