'Left join in SQL to identify certain rows

Given below is table A:

Cust_id Code
1 101
1 102
1 103
2 201

Table B:

Cust_id Code
1 101
1 102

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.

Cust_id Code
1 103

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
mysqlsqlmergeleft-join


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Thorsten Kettner
Solution 2 MatBailie

Related Questions