CODEWITHSUNDEEP
sqldb2
SRJCoding
SRJCoding

Reputation: 473

How to return values where one value is in both tables but another is in only one?

I have two tables, which look roughly like this:

Table1:

 CUSTOMER OFFER SALES
 -------- ----- -----
 A          111     1
 A          222     1
 B          111     1
 B          222     1
 C          111     1


Table2:

 CUSTOMER OFFER
 -------- -----
 A          111
 A          222
 B          111

I want to query these tables to return only the customers who are in both tables, but also to only return the rows where that combination of customer & offer does not exist in table2.

In other words, the results should look like this:

 CUSTOMER OFFER SALES
 -------- ----- -----
 B          222     1

Does anyone know how best to do this please?

Upvotes: 0

Views: 418

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use IN and EXCEPT as follows:

SELECT T1.* FROM T1 JOIN
(SELECT CUSTOMER, OFFER FROM T1
 WHERE T1.CUSTOMER IN (SELECT T2.CUSTOMER FROM T2)
EXCEPT
SELECT CUSTOMER, OFFER FROM T2) T11
ON T1.CUSTOMER = T11.CUSTOMER AND T1.OFFER = T11.OFFER

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271013

This sounds like exists and not exists:

select t1.*
from table1 t1
where exists (select 1
              from table2 t2
              where t2.customer = t1.customer
             ) and
      not exists (select 1
                  from table2 t2
                  where t2.customer = t1.customer and t2.offer = t1.offer
                 );

Upvotes: 1

Related Questions