CODEWITHSUNDEEP
mysql
pertrai1
pertrai1

Reputation: 4318

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 to no avail. Any help in figuring out how to find out what sample people have not become customers would be helpful. Thank you.

Upvotes: 1

Views: 149

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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)

Upvotes: 0

Jess
Jess

Reputation: 8700

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.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

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

Upvotes: 2

Related Questions