[SOLVED] Compare two tables, delete non matching values MYSQL

[dlebowski]

For some reason, I am struggling with the query, maybe someone can help me out.  Here is what I'm trying to do:

 

Before Query:

 

      Table 1                  Table 2

  ID  |  DATE              ID | DATE

-------------              -----------

1        2007-01-01      1      2007-01-01 

2        2007-01-01      1      2007-01-01 

3        2007-01-01      2      2007-01-01 

4        2007-12-12      3      2007-01-01 

5        2007-01-01      3      2007-01-01 

                                4      2007-12-12

 

After Query:

 

Table 1                  Table 2

  ID  |  DATE              ID | DATE

-------------          -----------

1        2007-01-01      1      2007-01-01 

2        2007-01-01      1      2007-01-01 

3        2007-01-01      2      2007-01-01 

4        2007-12-12      3      2007-01-01 

                                3      2007-01-01 

                                4      2007-12-12

 

I need the query to take the values in Table 1 and in Table 2 and compare them.  If Table 1 has an ID that doesn't exist anywhere Table 2 with the same date, then Table 1 needs to have that row removed. 

 

Any help would be greatly appreciated.  Please let me know if I need to elaborate. 

 

Ryan

 

[pikemsu28]

$query = "delete from table1 where id not in (select id from table2)";

[dlebowski]

Thank man.  Exactly what I needed.  I took it one step further to resolve my problem:

 

DELETE * from table1 where id not in (select id from table2 WHERE Date = '$date) AND Date = '$date'