help on mysql query for 2 tables

[pixeltrace]

hi,

 

i need help, i want to upload a csv file let say on

table 1 and later on compare table 1 with table 2 for some duplicates.

 

what is the sql query that will check fields in table 1 with table2 for

any duplicate items?

let say

select * from table1 where email=email of table2

what is the correct code for this?

 

also, what query to use to check fields in table1 with table2 for items

that are not yet existing in table 2?

let say

select * from table1 where email not equal to email of table2

what is the correct code for this?

 

hope you could help me with this.

 

thanks so much!

[btherl]

For duplicates

 

SELECT email FROM table1 JOIN table2 USING (email)

 

For emails in table1 but not in table2

 

SELECT email FROM table1 LEFT JOIN table2 USING (email) WHERE table2.id IS NULL

 

You can replace "id" with any column from table2.  You can also use this syntax which is more flexible:

 

SELECT email FROM table1 LEFT JOIN table2 ON (table1.email = table2.email) WHERE table2.email IS NULL

 

The reason it works is that a left join will place nulls in the columns of the right table where no matching row is found, so you just check for these nulls.  If table2 really CAN have nulls in that column, then of course this will not work.  Choose a column that can't be null.

[pixeltrace]

Thanks!

 

I'll try this out.