Case statement with Sum

Question

I want to identify all IDs from T1 for which T2 has order category of Veggie/Fruits but were never distributed. Also any IDs that do not have an order of Veggie/Fruits as well. How should I be doing this?

T1:
ID
1
2
3
4
5
6
7

T2
T2.ID  Category  Date Ordered    Date Distributed   T1.ID
01     Veggie    01-Jan-2018     01-Jan-2018        1
02     Fruits    02-Jan-2018     03-Jan-2018        1
03     Veggie    01-Jan-2018                        2
04     Nuts      05-Jan-2018     05-Jan-2018        2
05     Fruits    10-Jan-2018                        3
06     Seeds     10-Jan-2018     10-Jan-2018        3
07     Veggie    08-Jan-2018                        4
08     Veggie    11-Jan-2018     11-Jan-2018        5
09     Veggie    20-Jan-2018                        5
10     Fruit     12-Jan-2018     12-Jan-2018        5
11     Fruit     25-Jan-2018                        5
12     Nuts      15-Jan-2018     15-Jan-2018        6
13     Seeds     20-Jan-2018     22-Jan-2018        6
14     Nuts      18-Jan-2018                        7

I used a case statement with sum but I am not getting the desired result, the records like T1.2 and T1.3 are being dropped from my result. how should I be correcting my code?

select t1.id,
case when sum (
      case when t2.category in ('Veggie','Fruits') and t2.date_distributed is null
      then 0
      else 1 end)<1
then 1
else 0
end

Desired Output:

2
3
4
6
7

Answer 1

If I follow the logic correctly, exists comes to mind:

select *
from t1
where exists (select 1
              from t2
              where t2.id = t1.id and
                    t2.category in ('Veggie','Fruits') and
                    t2.date_distributed is null
             ) or
      not exists (select 1
                  from t2
                  where t2.id = t1.id and
                        t2.category in ('Veggie','Fruits') 
                 ) ;

EDIT:

I misread the data due to the original formatting of the question. For your question, I'll follow the same approach:

select *
from t1
where not exists (select 1
                  from t2
                  where t2.t1_id = t1.id and
                        t2.category in ('Veggie','Fruits') and
                        t2.date_distributed is not null
                 ) ;

You seem to want t1.ids where that are not in the specified categories with a distribution date.

Answer 2

You can make use of Not Exists logic by multiplication of two terms ;

1. decode(Category,'Veggie',1,'Fruits',1,0) is Zero, i.e. Not Veggie/Fruits or

2. nvl2(Date_Distributed,1,0) is Zero(null), i.e. Not Distributed

   with T2( T2_ID, Category, Date_Distributed, T1_ID ) as
   (
    select '01','Veggie', date'2018-01-01', 1 from dual union all   
    select '02','Fruits', date'2018-01-03', 1 from dual union all   
    select '03','Veggie',      null       , 2 from dual union all   
    select '04','Nuts'  , date'2018-01-05', 2 from dual union all   
    select '05','Fruits',      null       , 3 from dual union all   
    select '06','Seeds' , date'2018-01-10', 3 from dual union all    
    select '07','Veggie',      null       , 4 from dual union all   
    select '08','Veggie', date'2018-01-11', 5 from dual union all   
    select '09','Veggie',      null       , 5 from dual union all   
    select '10','Fruit' , date'2018-01-12', 5 from dual union all   
    select '11','Fruit' ,      null       , 5 from dual union all   
    select '12','Nuts'  , date'2018-01-15', 6 from dual union all   
    select '13','Seeds' , date'2018-01-22', 6 from dual union all   
    select '14','Nuts'  ,      null       , 7 from dual 
   )
   select T1_ID       
     from T2 
    group by T1_ID
    having sum(decode(Category,'Veggie',1,'Fruits',1,0)*nvl2(Date_Distributed,1,0))=0
    order by T1_ID;
   
   T1_ID
   -----
     2
     3
     4
     6
     7
   

By the way, there's no need any join with T1. If you want to use the table T1, the line from T2 can be replaced with from T1 join T2 on T1.ID = T2.T1_ID.

Rextester Demo

Answer 3

By joining the 2 tables and filtering:

select distinct t1.id from t1 inner join t2
on t2.t1id = t1.id
where
  (t2.category in ('Veggie','Fruits') and t2.date_distributed is null)
  or
  (t2.category not in ('Veggie','Fruits')); 

It is not clear in t2 table what the name of the column containing the id from t1 is so I used t2.t1id

Answer 4

Actually you want IDs for which doesn't exists distributed Veggie or Fruits.

So you use NOT EXISTS and in the subquery you consider only the records in the categories with date_distributed is NOT null

select *
from t1
where NOT exists (select null
              from t2
              where t2.t1id = t1.id and
                    t2.category in ('Veggie','Fruits') and
                    t2.date_distributed is NOT null
             );

        ID
----------
         2
         3
         4
         6
         7