Given below is table A:

Table B:

Table B has only customer 1 and contains only two product codes of customer 1. I want to identify the code of customers in Table B that is not present in Table B.

To get this, I did Table A left join Table B on cust_id and code and thought those with null values of code in Table B would give the desired result, but it did not seem to work. It would be really helpful if someone could tell what should the correct steps be. Thanks

My query so far:

select a.cust_id, a.code, b.cust_id as customer, b.code as product 
from a 
left join b on a.cust_id = b.cust_id and a.code = b.code

CodePudding user response：

Two conditions:

• cust_id must be in table B
• (cust_id, code) must not be in table B

The query with the conditions almost literally translated from human language to SQL:

select *
from a
where cust_id in (select cust_id from b)
and (cust_id, code) not in (select cust_id, code from b);

CodePudding user response：

• List item

• Hello other solution using exists

select a.cust_id, a.code
  from TABLEA a  left   join TABLEB  b on 
      a.cust_id=b.cust_id and a.code=b.code
 where b.cust_id is null and exists( select * from TABLEB 
      where cust_id=a.cust_id)