[RESOLVED] mysql compare 2 table with different field name

nuttynibbles

hi ppl, i need to compare 2 tables with different table structure but with 1 similar field which is email. basically i need to show records from one table that does not exist in another table. how should i do that??

NogDog

I think (but am not positive) you could do something like:

SELECT table1.userid, table1.email, table2.email
FROM table1
LEFT JOIN table2 on table1.email = table2.email
WHERE table2.email IS NULL

nuttynibbles

why do we check if WHERE table2.email IS NULL??

NogDog

With a left join, all rows in the first table (table1 in my example) will be returned by the join even if there is no match in the second table. In those cases, any fields selected from the second table will have a null value. So (if my theory is correct), the where clause will filter out results where there is a matching email address in the 2nd table, so you'll only get those result where the email is in the first table but not in the second.

nuttynibbles

wow ur right. i tested it out n it works.. tks so much..

nuttynibbles

hmm okie another Q. in table 1 i got username and email while table 2 i got passport number and email. the problem is, in table 1 for username field, some were entered as passport number while some were entered as email. so i need to check against table 2 if table1.username = table2.email or table1.username=table2.passport number.

nuttynibbles

hmm okie i think i managed to solve it. tks alot NogDog