如何制作一个花哨的mysql连接，可以连接三个表并检测一个表是否有我的项目

Question

php mysql query I have multiple linked tables - I also have a table that only creates and entry if certian conditions exist so I would like to add that into my query to avoid having to go through thousands of query searches looking for this special case

here is my current query

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

I then go through each item that was returned (in the thousands)

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']);
    }
    }

instead of all that I would like to just make it one query - pseduo code something like this

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

this way I dont have to return thousands I will only be returned the few that are not yet in the contract_sales table and I can go about my business...

Appreciate any help!

Answer 1

just check for NULL rows of c with a 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 ";