0

How to return 2 lines even if one line is null but the other > 0

 item_id  | location    | quantity   
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>
    21    |    1        |   <null>
    21    |    2        |   <null>

The result should return:

 item_id  | location    | quantity   
-------------------------------------
    14    |    1        |    10
    14    |    2        |   <null>

I need a query with a clause where quantity > 0. If quantity is null on location 1 and 2 do not return, if quantity > 0 in one location return both lines. I have 10 locations (1 to 10) and I need to look quantity where location in (1,2).

john_james
  • 3
  • 2
  • 2
    There may be options depending on the database product you are using. Please specify its name and version. – Andriy M Oct 28 '16 at 09:25

1 Answers1

3
select t1.*
from the_table t1
where t1.location_id in (1,2)
  and exists (select * 
              from the_table t2
              where t1.item_id = t2.item_id 
                and t2.quantity > 0
                and t2.location_id in (1,2));

The condition t2.quantity > 0 will be false for null values, so if all quantities for an item_id are null the where condition will be false, and the corresponding item will not be returned

Online example: http://rextester.com/NSRQ98002