How to compare two mysql tables of the same structure

Question

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.

Are you comparing them straight on `id`? or are you looking for lat, long pairs that exist in one table but not the other? I would recommend adding a few more rows and your expected results. — AdamMc331, May 01 '15 at 16:08
How large will the tables get? There is a very quick and simple method for small tables. — Misunderstood, May 01 '15 at 16:11
exactly !! looking for lat, long pairs that exist in table1 but not in table2 — crixus, May 01 '15 at 17:24
@crixus if that is that case, my answer should work alright. Does it give you problems? — AdamMc331, May 01 '15 at 18:29
$req= "SELECT adress FROM table1 WHERE (lat, long) NOT IN (SELECT lat,long FROM table2)"; $res=mysql_fetch_array($req); while ($res!=null) { echo' adress:'.$res['adress']; } — crixus, May 01 '15 at 18:48
Warning: mysql_fetch_array() expects parameter 1 to be resource — crixus, May 01 '15 at 18:51

score 2 · Answer 1 · answered May 01 '15 at 16:14

2

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.

answered May 01 '15 at 16:14

AdamMc331

16,492
10
71
133

william.eyidi · Answer 2 · 2017-03-06T12:07:19.693

2

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

?>

edited Mar 06 '17 at 12:07

answered May 01 '15 at 16:15

william.eyidi

2,315
4
27
39

thank's for answer, i tried your suggestion and had this error – crixus May 01 '15 at 17:40
1

object(mysqli_result)[2] public 'current_field' => null public 'field_count' => null public 'lengths' => null public 'num_rows' => null public 'type' => null – crixus May 01 '15 at 17:40
hey try this link for your problem, it seems like you have a problem instanciating your msqli variable http://stackoverflow.com/a/10424510/3871208 – william.eyidi May 01 '15 at 17:53
1

it's ok! i know where is the problem..thank u for your help ;) – crixus May 01 '15 at 21:27
Alright, don't forget to validate the answer. I glad to help. – william.eyidi May 01 '15 at 21:29

How to compare two mysql tables of the same structure

2 Answers2