finding differences in tables??

pleo

Ok, here's my deal. I have two tables full of peoples names. tableA has a bunch of names as does tableB. Some names overlap between tableA and tableB. So for example, tableA has the name "stevenson" as does tableB. However, tableB has some names that tableA does not. I want to know how to get the list of names in tableB that DO NOT exist in tableA. Any idea how to do this?? thanks!

LordShryku

Guessing this is MySQL, what version is it? If you have a newer version that allows subqueried, you can do

INSERT INTO tableA
SELECT name 
FROM tableB
WHERE name NOT IN (
      SELECT name FROM tableA
)

pleo

well that's not exaclty what i'm looking for. I want to find ALL the new names in tableB which DO NOT exist in tableA. does that make sense?

LordShryku

Then remove the insert

SELECT name 
FROM tableB
WHERE name NOT IN (
      SELECT name FROM tableA
)

pleo

ok, let me clarify this a bit more to avoid confusion.

So here it is

tableA tableB

name1 name7
name2 name8
name3 name9
name4 name1
name5 name2
name6 name3

all i want to see is:

tableB

name7
name8
name9

does this make sense??

pleo

ok, let me give that a try man

pleo

alright, so i gave this a shot and when i run it i get an error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT name
FROM tableA ) at line 5.

any suggestions?

LordShryku

Yeah, upgrade. Otherwise, give this a try. It will output the names not found

<?
$username = ""; //mysql user name
$password = ""; //mysql password
$server   = ""; //mysql server name
$database = ""; //database name
$tableA   = ""; //name of first table
$tableB   = ""; //name of second table
$fieldA   = ""; //name of the field from the first table
$fieldB   = ""; //name of the field from the second table

$conn=mysql_connect($server,$username,$password);
mysql_select_db($database,$conn);
$sql = mysql_query("SELECT ".$fieldB." 
                    FROM ".$tableB);
if(mysql_num_rows($sql) == 0) {
    die("No rows returned");
}

while($nm=mysql_fetch_array($sql)) {
    $cnt=mysql_query("SELECT count(*) 
                      FROM ".$tableA." 
                      WHERE ".$fieldA." = '".$nm[$fieldB]."'");
    $row=mysql_fetch_row($cnt);
    if($row[0] == 0) {
        echo $nm[$fieldB]." not found<br>";
    }
}
@mysql_close($conn);
?>

Quick and dirty. Hope it works 😃

pleo

thank you so much dude! that it exactly what i wanted. i owe you one! thanks a million

ultraslacker

Use OUTER JOINs.

SELECT a.name
FROM a
LEFT JOIN b
ON a.name = b.name
WHERE b.name IS NULL