php pdo代码比较两个表中的数据并返回结果

Question

我有两张 table

表1

图书收据优惠券

100 701 501,502

100 702 503,504

100 703 505,506

表2

书1收据1优惠券1

100 701 501,502

100 702 503,504

我正在根据bookno从table1中搜索数据。

现在我需要检查是否输入 bookno = 100 并单击搜索

然后检查table2的所有收据和优惠券...

并且只返回表2中不存在的值。

在上面的例子中

下面的行不存在于表2中，但存在于表1中

100 703 505,506

因此查询仅返回这一行......

请帮我做这个......

<?php   
$receipt = "";
$db=new PDO('mysql:host=localhost;dbname=circulation_scheme_prepaid','root','');
if($_POST && isset($_POST['search']))
{       
    $result=$db->prepare('SELECT DISTINCT receipt_no FROM scheme_master WHERE book_no2=:book_no2');
    $result->bindParam(':book_no2',$_POST['book_no']);
    $result->execute(); 
    $data1 = $result->fetchAll();
    $coupons = array(); 
        foreach($data1 as $row)
        {    
            $receipt[] = $row['receipt_no']; 

        }   
}
?>

Answer 1

我认为查询可能如下所示

    SELECT DISTINCT master.receipt_no 
    FROM scheme_master AS master 
        JOIN book_issue_return  AS bir  ON master.bookno2 = bir.book_no 
 WHERE master.book_no2=:book_no2  AND (
    bir.receiptno  NOT IN 
        (SELECT master.receipt_no FROM scheme_master AS master ) AND 
    bir.couponno NOT IN (SELECT master.receiptno FROM scheme_master AS master ))

然后:

$data1 = $result->fetchAll();

if (count($data1)) {
    // return all the data
} else {
    // return message
}