CODEWITHSUNDEEP
mysqlsqlselectdistinct
Adriana
Adriana

Reputation: 11

MySQL- Compare Customer IDs from two tables to determine who made no purchases

I have two tables in SQL, one with information on customers and orders placed by them (columns include customerid, contactname, orderid, quantity, to name a few). My second table is simply a list of all customer ids, and my task is to determine which customerID didn't make a purchase. Some of the customer ids made multiple purchases, so I am unsure of how to use SELECT DISTINCT to compare the two tables.

Upvotes: 1

Views: 194

Answers (2)

comphonia
comphonia

Reputation: 521

join the second table and filter the results 

    SELECT DISTINCT t1.customerid, t1.contactname
    FROM table1 t1
    JOIN table2 t2
    ON t1.customerid = t2.customerid
    WHERE t1.customerid = t2.customerid

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Use not exists:

select t2.customerid
from table2 t2
where not exists (select 1 from table1 t1 where t1.customerid = t2.customerid);

Upvotes: 0

Related Questions