mysql find difference from 2 tables

I need to find out how many people from our free samples table became customers in our customers table. Here is what I have for the 2 tables:

SELECT free_samples.id, 
free_samples.first_name, 
free_samples.last_name, 
free_samples.address, 
free_samples.city, 
free_samples.state, 
free_samples.zip
FROM free_samples

and then the customers table

SELECT customers.id, 
customers.firstName, 
customers.lastName, 
customers.address, 
customers.city, 
customers.state, 
customers.zip
FROM customers

Note that the id's are not the same so that makes it difficult to matc开发者_如何转开发h. So again, I need to know how many free sample requests actually became customers. Is there a way in with a query to identify this?

Thank you

SELECT COUNT(*) FROM free_samples AS fs, customers AS c 
    WHERE fs.first_name = c.first_name
      AND fs.last_name = c.last_name
      AND fs.address = c.address
      AND fs.city = c.city
      AND fs.state = c.state
      AND fs.zip = c.zip;

The query is pretty simple, but I think this is not a good way to do achieve your purpose.

Just add a new column 'free_samples_id' on 'customers' table to keep the id of 'free_samples' table. And record the id into the 'customers' table when a free user register as a customer.

You'll want to use INNER JOIN to get the results that have matches in both tables (i.e. people that were in free_samples AND made it to customers):

SELECT a.*
FROM customers a
    INNER JOIN free_samples b
        ON
            a.firstname=b.firstname
            AND a.city=b.city
            AND a.state=b.state
            AND a.zip=b.zip
            AND a.address=b.address
            AND a.lastname=b.lastname

NOTE: It's considered bad practice to SELECT *, I just wasn't sure exactly what data you'd need to pull.

I never did learn the difference between LEFT JOIN and RIGHT JOIN but I think this looks like a great situation to use it :P

Edit: Just seen the comment above me, and he's right. This code would actually get you customers who haven't converted their free sample into a membership. It's the exact opposite of what you wanted, but I'll leave it on the off-chance someone else may find it useful some time :)

=================

Basically you want something like

    SELECT c.first_name, c.last_name, c.address, c.city, c.state, c.zip
      FROM customers c
 LEFT JOIN free_samples fs
        ON c.first_name = fs.first_name
       AND c.last_name = fs.last_name
       AND c.zip = fs.zip

You may need to change LEFT JOIN to RIGHT JOIN, but I think one of those two should work. Someone else will come along and explain which one it should be, and why, I'm sure :)

If you want to compare any other fields, just add them to the bottom of the JOIN. It's important to note that the AND c.zip = fs.zip and so on aren't in the WHERE clause, they're conditions on the JOIN