php - 如何制作一个可以连接三个表并检测一个表是否没有我的项目的精美 mysql 连接

Question

php mysql 查询我有多个链接表 - 我还有一个表，只有在存在特定条件时才创建和输入，所以我想将其添加到我的查询中，以避免必须通过数千次查询搜索来寻找这种特殊情况

这是我当前的查询

$query = "SELECT a.UUID FROM contract a 
        INNER JOIN geoPoint b ON a.customer_UUID = b.customerUUID
        WHERE b.garcom_UUID = '$garbCom'
        AND b.city_UUID = '$city'";

然后我检查每件被退回的元素(数以千计)

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) 
{
    $sentdata = getothertable($row['UUID']); //checks if the item is in the table
             $sent = $sentdata ['senttoGarcom'];
             if($sent == 0) //if it wasn't found add it to my list
    {
       array_push($Contracts,$row['UUID']);
    }
    }

而不是所有我只想让它成为一个查询 - 像这样的伪代码

 $query = "SELECT a.UUID FROM contract a 
            INNER JOIN geoPoint b ON a.customer_UUID = b.customerUUID
            INNER JOIN contract_sales c ON a.UUID = c.contractUUID
            WHERE b.garcom_UUID = '$garbCom'
            AND b.city_UUID = '$city' AND c.DOESNOTEXIST";

这样我就不必退回数千，我只会退回 contract_sales 表中还没有的少数，我可以继续我的生意...

感谢任何帮助!

Answer 1

只需检查 NULL 行的 c 与 outer join

$query = "SELECT a.UUID FROM contract a 
        INNER JOIN geoPoint b ON a.customer_UUID = b.customerUUID
        LEFT OUTER JOIN contract_sales c ON a.UUID = c.contractUUID
        WHERE b.garcom_UUID = '$garbCom'
        AND b.city_UUID = '$city' AND c.contractUUID IS NULL ";