Comparing 2 MySQL tables
I have 2 tables in my database that I need to check to see if the person is not in one of them. I have one table that stores firstName, lastName, address, city, state, zip for samples. Then there is a customers table that also has this information. What I need to do is figure out what people from the samples have not become customers. I have not the first clue on how to run a query for this as i have tried multiple joins and what not开发者_StackOverflow社区 to no avail. Any help in figuring out how to find out what sample people have not become customers would be helpful. Thank you.
This attempts to match the person to customer based on first and last name (address is commented out, or add city, zip, state as required).
Where the join is not successful, it means the person never became a customer (not by the same first/last name at least) - this is tested using the c.firstname IS NULL
condition
select p.*
from person p
left join customer c on
on p.firstname = c.firstname
and p.lastname = c.lastname
#and p.address = c.address
where c.firstname is null
SELECT * FROM `tableA` LEFT JOIN `customers` ON `tableA`.`id`=`customers`.`id` WHERE `customers`.`id` IS NULL
It should work because when you left join it takes all records from both tables and connects them. If there isn't a record for the second table, it replaces the columns with null; therefore if they are not a customer the columns are null.
SELECT s.*
FROM samples s
WHERE NOT EXISTS(SELECT NULL
FROM customers c
WHERE s.firstName = c.firstName
AND s.lastName = c.lastName
AND s.address = c.address
AND s.city = c.city
AND s.state = c.state
AND s.zip = c.zip)
精彩评论