php - 使用 JOIN 选择 3 个表但不能选择 4 个？

Question

与我的 SQL SELECT 查询差不多，当我加入 3 个表时我可以看到答案，但是当我尝试加入最后的第 4 个表时我收到“0 结果”消息？这是 3 表查询:

$sql = "SELECT paymentPersonal.custID as paymentPersonalCust, paymentPersonal.firstname as paymentPersonalFname, paymentPersonal.lastname as paymentPersonalLname, paymentPersonal.mobile as paymentPersonalMobile, paymentPersonal.homephone as paymentPersonalHphone, paymentPersonal.email as paymentPersonalEmail, paymentsPayment.custID as paymentsPaymentCust, paymentsPayment.nameoncard as paymentsPaymentname, paymentsPayment.ccnumber as paymentsPaymentNumber, paymentsPayment.year as paymentsPaymentYear, paymentsPayment.month as paymentsPaymentMonth, paymentsPayment.code as paymentsPaymentCode, paymentsProduct.custID as paymentsProductCust, paymentsProduct.product as paymentsProduct from paymentPersonal join paymentsPayment on paymentsPayment.custID = paymentPersonal.custID join paymentsProduct on paymentPersonal.custID = paymentsProduct.custID where paymentPersonal.custID='$custID'";

$result = $conn->query($sql);

if ($result != null) 
{
     // output data of each row
     while($row = $result->fetch_assoc()) 
     {

        echo "<br> custID: ". $row["paymentPersonalCust"]. " - FirstName: ". $row["paymentPersonalFname"]. " - LastName: " . $row["paymentPersonalLname"] .  " - Mobile: ". $row["paymentPersonalMobile"]. " - Homephone: ". $row["paymentPersonalHphone"]. " - Email: ". $row["paymentPersonalEmail"]."<br>";

        echo "<br> custID: ". $row["paymentsPaymentCust"]. " - CcName: ". $row["paymentsPaymentname"]. " - CcNumber:  " . $row["paymentsPaymentNumber"] .  " - ccYear: ". $row["paymentsPaymentYear"]. " - ccMonth: ". $row["paymentsPaymentMonth"]. " - ccCode: ". $row["paymentsPaymentCode"]."<br>";

        echo "<br> custID: ". $row["paymentsProductCust"]. " - Product: ". $row["paymentsProduct"]."<br>";

     }
}

else {
     echo "0 results";
}

它确实有效并显示了所有期望的数据 ID 但是当我添加第 4 个表时它不再起作用并得到 else echo； “0 个结果”。这是我添加的 4 表结果:

$sql = "SELECT paymentPersonal.custID as paymentPersonalCust, paymentPersonal.firstname as paymentPersonalFname, paymentPersonal.lastname as paymentPersonalLname, paymentPersonal.mobile as paymentPersonalMobile, paymentPersonal.homephone as paymentPersonalHphone, paymentPersonal.email as paymentPersonalEmail, paymentsPayment.custID as paymentsPaymentCust, paymentsPayment.nameoncard as paymentsPaymentname, paymentsPayment.ccnumber as paymentsPaymentNumber, paymentsPayment.year as paymentsPaymentYear, paymentsPayment.month as paymentsPaymentMonth, paymentsPayment.code as paymentsPaymentCode, paymentsProduct.custID as paymentsProductCust, paymentsProduct.product as paymentsProduct, paymentsShipping.custID as paymentsShippingCust, paymentsShipping.address as paymentsShippingAddress, paymentsShipping.region as paymentsShippingRegion, paymentsShipping.city as paymentsShippingCity, paymentsShipping.postcode as paymentsShippingPostcode from paymentPersonal join paymentsPayment on paymentsPayment.custID = paymentPersonal.custID join paymentsProduct on paymentPersonal.custID = paymentsProduct.custID join paymentsShipping on paymentsShipping.custID = paymentsPayment.custID where paymentPersonal.custID='$custID'";

$result = $conn->query($sql);

if ($result != null) 
{
     // output data of each row
     while($row = $result->fetch_assoc()) 
     {

        echo "<br> custID: ". $row["paymentPersonalCust"]. " - FirstName: ". $row["paymentPersonalFname"]. " - LastName: " . $row["paymentPersonalLname"] .  " - Mobile: ". $row["paymentPersonalMobile"]. " - Homephone: ". $row["paymentPersonalHphone"]. " - Email: ". $row["paymentPersonalEmail"]."<br>";

        echo "<br> custID: ". $row["paymentsPaymentCust"]. " - CcName: ". $row["paymentsPaymentname"]. " - CcNumber:  " . $row["paymentsPaymentNumber"] .  " - ccYear: ". $row["paymentsPaymentYear"]. " - ccMonth: ". $row["paymentsPaymentMonth"]. " - ccCode: ". $row["paymentsPaymentCode"]."<br>";

        echo "<br> custID: ". $row["paymentsProductCust"]. " - Product: ". $row["paymentsProduct"]."<br>";

        echo "<br> custID: ". $row["paymentPersonalCust"]. " - Address: ". $row["paymentsShippingAddress"]. " - Region: " . $row["paymentsShippingRegion"] .  " - City: ". $row["paymentsShippingCity"]. " - postcode: ". $row["paymentsShippingPostcode"]."<br>"; 
     }
}

else {
     echo "0 results";
}

我不确定是我遗漏了一些小东西还是主要东西？？如果您有任何想法，请告诉我。谢谢!

Answer 1

如果没有数据样本，很难判断会发生什么，但是我首先要尝试的是将最终连接设为 LEFT JOIN 而不是常规 JOIN。对于常规 JOIN，数据需要存在于连接的两侧，而对于左连接，数据只需要存在于源表中。

事实上，如果您认为您的某些表很可能没有数据，我可能会建议将它们全部设为左连接。例如，您知道您有一位客户，但也许他们在您的联系表中没有电话号码。通过定期加入，您将无法看到您的客户，直到存在一个可能不受欢迎的电话号码。

最后，在进行左连接时，请准备好某些列为 NULL。