CODEWITHSUNDEEP
phpmysql
crixus
crixus

Reputation: 1

How to compare two mysql tables of the same structure

I have two tables with the same structure:

Table 1

---------------------------------------       
id    long        lat          adresse
---------------------------------------
1     8,767676    50,55599999  aaaaaa
2     3,77777777  40,55599999  xxxxxx
-
-
---------------------------------------

Table 2

---------------------------------------     
id    long        lat          adresse
---------------------------------------
1     3,77777777  40,55599999
2     
-
-
---------------------------------------

I want to compare Table 1 and Table 2 in php/mysql and display the adresse of the data row that doesn't have the same lat and long in Table 2.

From the example, display: aaaaaa.

Upvotes: 0

Views: 572

Answers (2)

william.eyidi
william.eyidi

Reputation: 2365

you can use a 'join'. 

query = "SELECT table_1.address
     FROM table_1
     JOIN table_2
     ON table_1.id = table_2.id
     WHERE table_1.lat != table_2.lat
     AND table_1.longitude != table_2.lat";

$mysqli = @new mysqli($host, $username, $password, $database);

if($result = $mysqli->query($query)){
    var_dump($result);
}

?>

you can also use a sub-query like this:

<?php

query = "SELECT address
         FROM table_1
         WHERE (long, lat) NOT IN (SELECT long, lat FROM table_2)";

$mysqli = @new mysqli($host, $username, $password, $database);

if($result = $mysqli->query($query)){
    var_dump($result);
}

?>

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16691

To get the address of pairs from table one that don't exist in table two, you can use the NOT IN operator, like this:

SELECT address
FROM t1
WHERE (longitude, lat) NOT IN (SELECT longitude, lat FROM t2);

Here is an SQL Fiddle example.

Upvotes: 2

Related Questions