CODEWITHSUNDEEP
sqloracle-databasesybase
Icebreaker
Icebreaker

Reputation: 287

SQL query to return data only if ALL necessary columns are present and not NULL

ID | Type     | total
1    Purchase   12
1    Return     2
1    Exchange   5
2    Purchase   null
2    Return     5
2    Exchange   1
3    Purchase   34
3    Return     4
3    Exchange   2
4    Purchase   12
4    Exchange   2

Above is sample data. What I want to return is:

ID | Type     | total
 1    Purchase   12
 1    Return     2
 1    Exchange   5
 3    Purchase   34
 3    Return     4
 3    Exchange   2

So if a field is null in total or the values of Purchase, Return and Exchange are not all present for that ID, ignore that ID completely. How can I go about doing this?

Upvotes: 1

Views: 303

Answers (4)

Nader Hisham
Nader Hisham

Reputation: 5414

This also should work fine even if new values are added to type column

select * from t where 
ID not in(select ID from t where 
t.total is null or t.[Type] is null)

Upvotes: 1

user5683823
user5683823

Reputation:

Analytic functions are a good way to solve this kind of problems. The base table is read just once, and no joins (explicit or implicit, as in EXISTS conditions or correlated subqueries) are needed.

In the solution below, we count distinct values of 'Purchase', 'Exchange' and 'Return' for each id while ignoring other values (assuming that is indeed the requirement), and separately count total nulls in the total column for each id. Then it becomes a trivial matter to select just the "desired" rows in an outer query.

    with
         test_data ( id, type, total ) as (
           select 1, 'Purchase', 12   from dual union all
           select 1, 'Return'  , 2    from dual union all
           select 1, 'Exchange', 5    from dual union all
           select 2, 'Purchase', null from dual union all
           select 2, 'Return'  , 5    from dual union all
           select 2, 'Exchange', 1    from dual union all
           select 3, 'Purchase', 34   from dual union all
           select 3, 'Return'  , 4    from dual union all
           select 3, 'Exchange', 2    from dual union all
           select 4, 'Purchase', 12   from dual union all
           select 4, 'Exchange', 2    from dual
         )
    -- end of test data; actual solution (SQL query) begins below this line
    select id, type, total
    from   ( select id, type, total,
                    count( distinct case when type in ('Purchase', 'Return', 'Exchange')
                                         then type end                         
                         ) over (partition by id)             as ct_type,
                    count( case when total is null then 1 end
                         ) over (partition by id)             as ct_total
             from   test_data
           )
    where  ct_type = 3 and ct_total = 0
    ;

Output:

    ID  TYPE      TOTAL
    --  --------  -----
     1  Exchange      5
     1  Purchase     12
     1  Return        2
     3  Exchange      2
     3  Purchase     34
     3  Return        4

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562388

I would write this as a join, without subqueries:

SELECT pur.id, pur.total AS Purchase, exc.total AS Exchange, ret.total AS Return
FROM MyTable as pur
INNER JOIN MyTable AS exc ON exc.id=pur.id AND exc.type='Exchange'
INNER JOIN MyTable AS ret ON ret.id=pur.id AND ret.type='Return'
WHERE pur.type='Purchase'

The inner join means that if any of the three rows with different values are not found for a given id, then no row is included in the result.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use exists. I think you intend:

select t.*
from t
where exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Purchase' and t2.total is not null
             ) and
      exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Exchange' and t2.total is not null
             ) and
      exists (select 1
              from t t2
              where t2.id = t.id and t2.type = 'Return' and t2.total is not null
             );

There are ways to "simplify" this:

select t.*
from t
where 3 = (select count(distinct t2.type)
           from t t2
           where t2.id = t.id and
                 t2.type in ('Purchase', 'Exchange', 'Return') and
                 t2.total is not null
          );

Upvotes: 2

Related Questions