开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜