Comparing two mySql tables to find differences

[asaschool]

Hey everyone,

 

I have been on the road for the last 18 days and I am not scheduled to be home for another 10 days. I dont think my brain is working today. I use mysql everyday, but for some reason I am having problems finding new records in my database. Here is what is happening. I download data everyday from a data source and I need to find new records that are entered into this data source. To do this I created two tables with the same feilds in both. When I download the source data I put it in one table called "new" - I then want to compare the two databases and find all the new records so that I can email the new ones to selected people. Then once that is complete I move the data located in "new" to the main table and delete all the data located in "new". I have tried doing a loop with php that looks for new records by trying:

 

PHP Code:

<?php  if ($oldMls <> $newMls) { do {?>

  <?php echo $row_rsNew['mls_num']; ?><br />

  <?php } while ($row_rsNew = mysql_fetch_assoc($rsNew)); } ?>>

But this displays all records not the new ones (something tells me that the if statement should be located in the loop instead of starting the loop?)

 

I have also tried doing this task in the mysql query by trying:

 

SELECT tableNew.data, tableMain.data

FROM tableNew, tableMain

WHERE tableMain.data <> tableNew.data

GROUP BY tableNew.data

 

This is not working right either.

 

I dont think I am heading in the right direction with this. Can anybody put me back on the right track with the best way to achieve this?

 

Thanks in advance!

[lemmin]

This should work:

$qry="SELECT data FROM tableNew WHERE NOT EXISTS (SELECT data FROM tableMain WHERE data = tableNew.data)";

[asaschool]

Thank you for your input. I looked into going in that direction but I kept getting an error everytime I ran the script. During that process I remembered how to do a sql query that would work for me. Here is the solution that I found to work.

 

SELECT data

FROM tableOne LEFT JOIN tableTwo ON tableOne.data = tableTwo.data

WHERE tableOne.data IS NULL

 

Thank you very much for your time and input