Search code examples
joinderby

Doing a left join on the union of two tables where the table being joined is one of the ones in the union

I have two tables called "wants" and "has_wanted" as shown below:

wants

email id
chris 219
chris 222

has_wanted

email id
chloe 219
chloe 221
chloe 221
chloe 222
chloe 223
chris 219
chris 221
mike 220
mike 221
mike 223

I want to select the item id's which have the email 'chris' that are in either table such that 'mike' in the 'has_wanted' table does not also have the id's. The figure below shows what I am trying to do.

[venn diagram of the result set I am trying to achieve][1] My first attempt was to use a join as desired below: 

select o.email, o.id from  
(  
   select email,id from has_wanted  
   where email = 'chris'  
   union  
   select email, id from wants  
   where email = 'chris'  
)  
as o  
left join has_wanted 
on o.id = has_wanted.id  
where has_wanted.email = 'mike'  
and  
has_wanted.id is null

I expected id#'s {219,222} to be in the result. However, I get zero results. Any suggestions about where I am going wrong? I am using Derby for as my database.

Solution

  • Try using a not in

    select o.email, o.id 
from 
( 
  select email,id 
  from has_wanted 
  where email = 'chris' 
  union 
  select email, id 
  from wants 
  where email = 'chris' 
) as o 
Where o.id not in 
(
  Select id 
  from has_wanted 
  where has_wanted.email = 'mike'
)