How to make a fancy mysql join that can join three tables and detect if one table DOESNT have my item


Problem :

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!



Solution :

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

Mysql Tutorials

Mysql Howto..

How to automatically import xlsx-file in mysql database

PHP MYSQL FULLTEXT how to make a Precise search?

How to show all the tables from multiple databases

How import from sql file to mysql db in ruby on rails using cloud9?

How to save captured image path from imageview to mysql database

How can I filter my MySQL results by their content? i.e. if contains hashtags

How to troubleshoot mysql_error()?

How to sort perfectly a select from a mysql database

How to find Mysql thread

How to display single quotes on MySQL POST [closed]

How to sum *count* subqueries with SQLAlchemy?

How do I find and replace individual strings in a MySQL database?

How to get records for the most recent complete 13 weeks?

How do i perform a search in mysql with a substring, instead of full string?

MySQL: How to copy column from one table without id into another table?

How to display images with fixed dimensions in ASP.NET after retrieving them from a MySQL database

how to paginate the retrieved data from mysql database by using php

How to reverse data of a column into another column in MySql?

How to get the trending tags using php and mysql?

MySQL Query - How to count foreign key occurence from second table with three table connected?

AWS rds - How to read from a read replica inside of a Java application?

How to check MySQL results during a 'while' table generation

I have 2 MySql tables. How to get not all data from them in 1 sql query?

How to get the last record before the last one in mysql?

MySQL: How to select rows which have the lowest value in a field?

How to fix “Incorrect string value” errors?

How do I code this SQL query in Laravel? Correct way to code mySQL HAVING

How to distribute ranking using MySQL Query and PHP

Count how often a row contains a keyword

MySQL how to conditionally exclude records when grouping