Left outer join of 3 tables

Question

I am trying to get the count of distinct people of the shaded region.

Table structure is as follows:

    customer       key
    A234            1
    A345            4
    A12             5
    A989            6

HIVE Query:

    select count(distinct(a.customer)) 
    from (
        select *
         from cust
         where key in (1,2,3)) c 
    left outer join (
        select *
         from cust
         where key in (4,5)) a on a.customer= c.customer where c.customer is null
         join
            (select *
             from cust
             where key in (6,7,8,9)) d on c.customer = d.customer and d.customer is null;

Error:

missing EOF at 'join' near 'null'

Answer 1

You have a syntax problem because where follows the from clause, and a given select has only one where.

I would just use group by and having. To get the customers:

select c.customer
from cust c
group by c.customer
having sum(case when key in (1, 2, 3) then 1 else 0 end) > 0 and
       sum(case when key in (4, 5, 6, 7, 8, 9) then 1 else 0 end) = 0;

You can then count them with a subquery:

select count(*)
from (select c.customer
      from cust c
      group by c.customer
      having sum(case when key in (1, 2, 3) then 1 else 0 end) > 0 and
             sum(case when key in (4, 5, 6, 7, 8, 9) then 1 else 0 end) = 0
    ) c